I have the following query:
SELECT
a.name, a.address, n.date, n.note
FROM a
LEFT JOIN n ON a.id = n.id
The a.id has a one to many relationship with n.id, so that many notes can be assocaited with one name.
How do I return just the latest note for each name instead of all the notes?
I'm using SQL Server 2008.
Thanks.
Here's one way using ROW_NUMBER()
SELECT t.name, t.address, t.date, t.note
FROM (
SELECT
a.name, a.address, n.date, n.note,
ROW_NUMBER() OVER (PARTITION BY a.name ORDER BY n.date DESC) rn
FROM a
LEFT JOIN n ON a.id = n.id
) t
WHERE t.rn = 1
alternative you can use a correlated subquery too get the max date, something like this
SELECT
a.name, a.address, n.date, n.note
FROM a
LEFT JOIN n ON a.id = n.id
WHERE n.date = (SELECT MAX(nn.date)
FROM n AS nn
WHERE a.id = nn.id)
OR n.date IS NULL to the main query's WHERE clause