SQL joined by last date

Question

This is a question asked here before more than once, however I couldn't find what I was looking for. I am looking for join two tables, where the joined table is set by the last register ordered by date time, until here all is ok.

My trouble start on having more than two records on the joined table, let me show you a sample

table_a
-------
id
name
description
created
updated

table_b
-------
id
table_a_id
name
description
created
updated

What I have done at the beginning was:

SELECT a.id,  b.updated
FROM table_a AS a 
LEFT JOIN (SELECT table_a_id, max (updated) as updated 
           FROM table_b GROUP BY table_a_id ) AS b 
ON a.id = b.table_a_id

Until here I was getting cols, a.id and b.updated. I need the full table_b cols, but when I try to add a new col to my query, Postgres tells me that I need to add my col to a GROUP BY criteria in order to complete the query, and the result is not what I am looking for.

I am trying to find a way to have this list.

You shouldn't accept a syntactically incorrect answer. That's fooling others looking for help. — Erwin Brandstetter, Jun 24 '13 at 18:51
For been honest, both of them questions work for me, as I don't have enough knowledge for this issue, I couldn't know which one of them is right or syntactically , I just accept the first one, and after read, I am thinking on use your solution and learn more on it, but must no be right on take off the answer choose. — Carlos, Jun 24 '13 at 19:37
"Both"? There are three answers, you accepted the one that doesn't work. — Erwin Brandstetter, Jun 24 '13 at 19:39
@Erwin, your answer and the other one that I have accept work for me, the other is similar that I have chose — Carlos, Jun 24 '13 at 21:35
`select distinct on(table_a_id) as table_a_id` cannot work. It's invalid. If you want to accept that answer, that's up to you. But see to it, that you get it fixed then. If you cannot edit yourself, (you probably don't have the privileges yet), leave a note for Gordon to fix it. Anyway, I'll do it myself, just wanted to get the message across. — Erwin Brandstetter, Jun 24 '13 at 21:55

score 2 · Answer 1 · edited Jun 24 '13 at 21:55

2

You can use Postgres's distinct on syntax:

select a.id, b.*
from table_a as a left join
     (select distinct on (table_a_id) table_a_id, . . . 
      from table_b
      order by table_a_id, updated desc
     ) b
     on a.id = b.table_a_id

Where the . . . is, you should put in the columns that you want.

edited Jun 24 '13 at 21:55

Erwin Brandstetter

605,456
145
1,078
1,228

answered Jun 24 '13 at 14:37

Gordon Linoff

1,242,037
58
646
786

I have changed my choice on the answer, cause I was reading some thing more about it and the Erwin question was the right question. I hope not dissapoint to you on my choice, also mark, but Erwin give me more options and explanation. Thanks a lot for you time – Carlos Jun 26 '13 at 16:44

score 2 · Answer 2 · 2013-06-24T16:09:25.470

2

Try:

SELECT a.id,  b.*
FROM table_a AS a 
LEFT JOIN (SELECT t.*, 
                  row_number() over (partition by table_a_id 
                                     order by updated desc) rn
           FROM table_b t) AS b 
ON a.id = b.table_a_id and b.rn=1

edited Jun 24 '13 at 16:09

answered Jun 24 '13 at 14:39

I have tried this answer, however told me that t.* must be included on the GROUP BY – Carlos Jun 24 '13 at 14:52
@Carlos: Sorry, the GROUP BY should **not** have been included in the query - I have corrected it now. – Jun 24 '13 at 16:10
Thanks a lot for your time, I have choosen Erwin answer cause it have more explanation, however it is similar to your answer. Thanks a lot – Carlos Jun 26 '13 at 16:47

score 2 · Accepted Answer · edited May 23 '17 at 12:27

DISTINCT ON or is your friend. Here is a solution with correct syntax:

SELECT a.id, b.updated, b.col1, b.col2
FROM   table_a as a
LEFT   JOIN (
   SELECT DISTINCT ON (table_a_id)
          table_a_id, updated, col1, col2
   FROM   table_b
   ORDER  BY table_a_id, updated DESC
   ) b ON a.id = b.table_a_id;

Or, to get the whole row from table_b:

SELECT a.id, b.*
FROM   table_a as a
LEFT   JOIN (
   SELECT DISTINCT ON (table_a_id)
          *
   FROM   table_b
   ORDER  BY table_a_id, updated DESC
   ) b ON a.id = b.table_a_id;

Detailed explanation for this technique as well as alternative solutions under this closely related question:
Select first row in each GROUP BY group?

I have a big issue related to performance, I have post that here http://stackoverflow.com/questions/18545911/getting-last-rows-by-date-on-a-large-postgresl-data-base — Carlos, Aug 31 '13 at 07:58

SQL joined by last date

3 Answers3

Linked