PostgreSQL Subquery join and one value

Question

Im not PostgreDev, got problem with returning just one value in subquery.

select * from
(
select m_id from TableA where m_id = 236779

)Main
inner  join
(
select m_m_id as l_m_id,date_created as l_date_created
from TableB
where
proc_type <> '-'
order by date_created desc limit 1
) CheckLastCode on (Main.m_id = CheckLastCode.l_m_id)

Will return empty set.

When I take down limit 1

select * from
(
select m_id from TableA where m_id = 236779

)Main
inner  join
(
select m_m_id as l_m_id,date_created as l_date_created
from TableB
where
proc_type <> '-'
order by date_created desc
) CheckLastCode on (Main.m_id = CheckLastCode.l_m_id)

Will return all from TableB.

Im trying to have just last value from tableB

@EDIT It should work for every m_id in tableA

So my output: M_ID | MAX(DATE_CREATED) for that M_ID| ...

Linger · Accepted Answer · 2013-12-03 15:03:29Z

1

Here is the SQL Fiddle that demonstrates the following query:

SELECT * 
FROM TableA AS a
  JOIN TableB as b 
  ON a.m_id = b.m_m_id AND b.date_created = 
  (
    SELECT MAX(bs.date_created) 
    FROM TableB bs
    WHERE bs.m_m_id = a.m_id
    LIMIT 1
  )

If your tables have a lot of records you may want to apply a range in a WHERE clause to speed up the query, like so:

SELECT * 
FROM TableA AS a
  JOIN TableB as b 
  ON a.m_id = b.m_m_id AND b.date_created = 
  (
    SELECT MAX(bs.date_created) 
    FROM TableB bs
    WHERE bs.m_m_id = a.m_id
    LIMIT 1
  )
WHERE a.m_id BETWEEN 2 AND 3

edited Dec 3, 2013 at 15:03

answered Dec 3, 2013 at 14:05

Linger

15.1k23 gold badges54 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

VoonArt Over a year ago

Well I added just one ID for testing purposes. It should work for every M_ID. ID | MAX(DATE) for that ID| ... for that ID

VoonArt Over a year ago

It will work only for M_ID = 236779. I need to make it working for each m_id in table select * from ( select m_id from TableA --return 8kk m_id's )Main inner join ( select m_m_id as l_m_id,date_created as l_date_created from TableB where proc_type <> '-' order by date_created desc limit 1 ) CheckLastCode on (Main.m_id = CheckLastCode.l_m_id)

Linger Over a year ago

@VoonArt, I just updated my answer to apply to your recently updated question.

Jorge Campos · Accepted Answer · 2013-12-03 14:30:55Z

0

Change the second query to:

select m_m_id as l_m_id,date_created as l_date_created
  from TableB
 where proc_type <> '-'
   and date_created = (select max(date_created) 
                        from TableB 
                        where m_m_id = 236779 limit 1)

edited Dec 3, 2013 at 14:30

answered Dec 3, 2013 at 14:05

Jorge Campos

23.9k7 gold badges65 silver badges95 bronze badges

3 Comments

Linger Over a year ago

If you are getting the max(date_created) from a sub query, you also have to specify the WHERE clause of m_m_id = 236779, otherwise you may retrieve an unrelated date_created value.

Linger Over a year ago

@Jorge Campos, actually it appears that l_m_id is the alias. The actual name of the field is m_m_id.

Jorge Campos Over a year ago

Yeaaah, you are right, my mistake. again. =/ I will correct again. Thanks.

Collectives™ on Stack Overflow

PostgreSQL Subquery join and one value

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related