CODEWITHSUNDEEP
sql
scatman
scatman

Reputation: 14565

Help in sql query

I have a table:

ID  Title   Date
30   1     10/10/2010
20   1     10/11/2010
40   2     01/01/2010

i need a way to select distinct titles with max dates.
so when title is 1, i should have the second row selected since the date in the 1st row is smaller than the date in the second row.
as a result the query should return:

20 1 10/11/2010
40 2 01/01/2010

Upvotes: 1

Views: 71

Answers (3)

Adam Flanagan
Adam Flanagan

Reputation: 3052

select
max(ID), Title , max(Date)
from
table
group by
Title

Upvotes: 0

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

Your data set is unclear; do you want the minimum ID and the maximum date?

SELECT ID=MIN(ID), Title, [Date] = MAX([Date])
FROM Table
GROUP By Title

Or was it a typo, and you want the row with the maximum date (assuming that the dates may not follow the ID value)?

SELECT  a.ID
      , a.Title
      , a.Dt
FROM    Table a
        JOIN ( SELECT   b.Title
                      , Dt = MAX(Dt)
               FROM     Table b
               GROUP BY Title
             ) c ON a.Dt = c.Dt
                    AND a.Title = c.Title

Upvotes: 2

Pavel Urbančík
Pavel Urbančík

Reputation: 1496

select max(ID) as ID, max(Date), Title
from table
group by Title

should do the trick

Upvotes: 2

Related Questions