CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
lidermin
lidermin

Reputation: 2832

Query SQL distinct field with max date, returning all fields from table

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

Answers (3)

GolezTrol
GolezTrol

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

Lamak
Lamak

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

Matthew
Matthew

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

Related Questions