Select row with MAX date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select row with MAX date

Author

Topic

srussell
Starting Member

9 Posts

Posted - 2008-09-04 : 11:22:29

I am trying to select a row in a table with the max date according to an id :

select * from table1 where id = '1' and myDate = MAX(myDate)

What am I doing wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-04 : 11:25:14

select y.* from table1 as y where y.id = '1' and y.myDate = (SELECT MAX(t1.myDate) FROM Table1 AS t1 WHERE t.id = y.id)

E 12°55'05.63"
N 56°04'39.26"

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-09-04 : 11:27:27

Use a subquery:

select * 
from table1
where id = '1'
and myDate = (
    select max(myDate)
    from table1
    where id = '1'
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 11:47:39

[code]SELECT TOP 1 *
FROM Table1
WHERE id='1'
ORDER BY myDate DESC[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-04 : 11:49:34

What about duplicate dates visakh?

SELECT TOP 1 * with ties
FROM Table1
WHERE id='1'
ORDER BY myDate DESC

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 12:00:43

quote:
Originally posted by Peso

What about duplicate dates visakh?

SELECT TOP 1 * with ties
FROM Table1
WHERE id='1'
ORDER BY myDate DESC

E 12°55'05.63"
N 56°04'39.26"

OP asked for a row with max date

srussell
Starting Member

9 Posts

Posted - 2008-09-04 : 12:14:53

quote:

Originally posted by bjoerns

Use a subquery:

select * 
from table1
where id = '1'
and myDate = (
    select max(myDate)
    from table1
    where id = '1'
)

Thanks this worked great!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources