CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
Pierre P.
Pierre P.

Reputation: 888

Getting the last entry within a join

I know this has been asked a lot but I can't seem to get my query working.

I'm trying to get only one row per id in a query looking like this : 

SELECT a.id, b.name
FROM table1 a
LEFT JOIN table2 b ON a.key = b.key
WHERE a.Date = 
(SELECT MAX(a1.date) from table1 WHERE a1.primarykey = a.primarykey)
GROUP BY a.id, b.name

I do not need to group by b.name but have to since I need to group by id.

Right now, I have multiple occurences for b.name which duplicates a.id where I just want the corresponding b.name for the last date for a.id.

Can anyone point me to the right way to do this ?

Thank you

Upvotes: 0

Views: 57

Answers (3)

forpas
forpas

Reputation: 164099

I guess this condition:

WHERE a1.primarykey = a.primarykey

should be:

WHERE a1.key = a.key

and key is not the primary key of table1, because if you really mean the primary key then there is no point to search for the MAX(date) for the primary key since there is only 1 date for each primary key.
If I'm not wrong then try with row_number():

SELECT t.id, t.name
FROM (
  SELECT a.id, b.name,
    row_number() over (partition by a.key order by a.date desc) rn
  FROM table1 a LEFT JOIN table2 b 
  ON a.key = b.key
) t 
WHERE t.rn = 1

Upvotes: 1

EragonBY
EragonBY

Reputation: 43

try this:

SELECT a.id, b.name from (
SELECT a1.id,a1.key,
  rank() over(partition by a1.key order by a1.date desc) md FROM table1 a1 )a
LEFT JOIN table2 b ON a.key = b.key and a.md=1;

but I don't get -you need group by Id or key, double check it

Upvotes: 0

kgaams
kgaams

Reputation: 1

It looks like you would be getting 1 row per id if you would be removing b.name from your group statement.

Not sure why you would need to group on b.name if you group on a.id?

Upvotes: 0

Related Questions