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
– Robb, Commented Nov 30, 2010 at 13:10
possible duplicate of stackoverflow.com/questions/121387/… — Brandon Montgomery
– Brandon Montgomery, Commented Nov 30, 2010 at 13:19

Stuart Ainsworth · Accepted Answer · 2010-11-30 13:18:35Z

2

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

answered Nov 30, 2010 at 13:18

Stuart Ainsworth

12.9k44 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pavel Urbančík · Accepted Answer · 2010-11-30 13:10:23Z

2

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

should do the trick

answered Nov 30, 2010 at 13:10

Pavel Urbančík

1,4969 silver badges6 bronze badges

2 Comments

Robb Over a year ago

This is valid for the data sample but breaks when the max date isn't on the row with the max id

scatman Over a year ago

but its not always true that the largest id have the largest date.... i will edit the table...

Adam Flanagan · Accepted Answer · 2010-11-30 13:42:27Z

0

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

edited Nov 30, 2010 at 13:42

answered Nov 30, 2010 at 13:12

Adam Flanagan

3,05226 silver badges31 bronze badges

3 Comments

Stuart Ainsworth Over a year ago

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).

Robb Over a year ago

grouping by the ID which is unique will result in returning all rows

Stuart Ainsworth Over a year ago

Still not quite there :) You have to do something with the ID column now. Either GROUP with it, or use it in an aggregate.

Collectives™ on Stack Overflow

Help in sql query

3 Answers 3

Comments

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related