CODEWITHSUNDEEP
sqlsql-servert-sql
Nick LaMarca
Nick LaMarca

Reputation: 8188

Selecting Only The Latest Version Of A Field

I have a table schema with ID, Title, Date

and data looks something like this:

1  The title1    2011-04-05 00:00:00.000
2  Another title 2011-04-11 00:00:00.000
3  The title1    2011-04-11 16:49:23.633
4  The title1    2011-04-11 00:00:00.000

I need help with sql to give me the latest date of each title. So the output for the above data is 

2  Another title 2011-04-11 00:00:00.000
3  The title1    2011-04-11 16:49:23.633

Upvotes: 0

Views: 2127

Answers (6)

openshac
openshac

Reputation: 5165

select mytable.Title, MAX(mytable.Date)
from mytable
group by mytable.Title

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

Assuming at least SQL Server 2005:

SELECT ID, Title, [Date]
    FROM (SELECT ID, Title, [Date],
                 ROW_NUMBER() OVER(PARTITION BY Title ORDER BY [Date] DESC) AS RowNum
              FROM YourTable
         ) t
    WHERE t.RowNum = 1

Upvotes: 1

Steve Mayne
Steve Mayne

Reputation: 22818

Assuming your table name is mytable:

select 
  tbl.ID, tbl.Title, tbl.Date
from 
  mytable tbl join
  (select Title, max(Date) Date
   from mytable) maxes on tbl.Date = maxes.Date and tbl.Title = maxes.Title

Upvotes: 1

asharajay
asharajay

Reputation: 1193

select id,title,date from [schema]
where title in (select title from [schema] group by title having date=max(date))

try this

Upvotes: 5

SQLMenace
SQLMenace

Reputation: 135021

Take a look at Including an Aggregated Column's Related Values, there are several ways to accomplish this

One way would be

select t1.* from(
select Title, Max(Date) as MaxDate
From YourTable
group by Title) t2
join YourTable t1 on t2.Title = t1.Title
and t1.Date = t2.MaxDAte

Upvotes: 1

HLGEM
HLGEM

Reputation: 96572

Look at Max and group by in Books online

Upvotes: 2

Related Questions