Reputation: 2832
I'm having a hard time trying to get a query that sounds simple. I'm using SQL Server 2008.
I have this table:
ID Name Date Zone
----------------------------
01 AAA 01/01/2010 North
02 BBB 02/02/2010 South
03 AAA 05/01/2010 West
So, I need to filter the table to get distinct items (by Name), but, in case the item is repeated, get the one with the max date. Here is the result that I wanted to get from the example above:
ID Name Date Zone
----------------------------
02 BBB 02/02/2010 South
03 AAA 05/01/2010 West
The problem is that I need to get all the fields from the table (I mean: Id, Name, Date and Zone should be returned from the query) once applied the filter.
Upvotes: 1
Views: 10986
Reputation: 116200
select
t.ID,
t.Name,
t.Date,
t.Zone
from
ThisTable t
inner join
(select
Name,
max(Date) as Date
from
ThisTable
group by
Name) x on x.Name = t.Name and x.Date = t.Date
Upvotes: 4
Reputation: 70678
Try this:
;WITH CTETable AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY [Date] DESC) ID
FROM YourTable
)
SELECT *
FROM CTETable
WHERE ID = 1
Upvotes: 6
Reputation: 10444
SELECT a.columns
FROM table a
WHERE a.ID IN
(SELECT TOP 1 b.id FROM table b WHERE b.Name = a.Name ORDER BY b.Date DESC)
This will return the most recent row for each unique name.
Upvotes: 2