CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Carlos
Carlos

Reputation: 4637

SQL joined by last date

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.

Upvotes: 1

Views: 146

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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.

Upvotes: 2

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659297

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?

Upvotes: 2

user359040
user359040

Reputation:

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

Upvotes: 2

Related Questions