Help in sql query

Question

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

Your results set shows the ID of the first row with the date of the second. Is this what you want or a typo? — Robb, Nov 30 '10 at 13:10
possible duplicate of http://stackoverflow.com/questions/121387/sql-fetch-the-row-which-has-the-max-value-for-a-column — Brandon Montgomery, Nov 30 '10 at 13:19

score 2 · Answer 1 · answered Nov 30 '10 at 13:10

2

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

should do the trick

answered Nov 30 '10 at 13:10

Pavel Urbančík

1,466
9
6

1

This is valid for the data sample but breaks when the max date isn't on the row with the max id – Robb Nov 30 '10 at 13:12
but its not always true that the largest id have the largest date.... i will edit the table... – scatman Nov 30 '10 at 13:12

score 2 · Accepted Answer · answered Nov 30 '10 at 13:18

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

Adam Flanagan · Answer 3 · 2010-11-30T13:42:27.317

0

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

edited Nov 30 '10 at 13:42

answered Nov 30 '10 at 13:12

Adam Flanagan

3,052
23
31

This won't work; you're grouping by the ID and the Title, which means all rows will be returned (assuming that the ID is unique). – Stuart Ainsworth Nov 30 '10 at 13:22
grouping by the ID which is unique will result in returning all rows – Robb Nov 30 '10 at 13:23
1

Still not quite there :) You have to do something with the ID column now. Either GROUP with it, or use it in an aggregate. – Stuart Ainsworth Nov 30 '10 at 13:28

Help in sql query

3 Answers3