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 ti开发者_开发问答tle 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

---

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

try this

---

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

---

Look at Max and group by in Books online

---

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

---

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

---

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