Max of a Max? - 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)

Max of a Max?

Author

Topic

disneybum
Starting Member

1 Post

Posted - 2008-12-16 : 04:16:28

I know I should know this, but I'm drawing an absolute blank and don't have time to figure it out.

A table has three fields; a name field, time field, and date field.I need to get the name associated with the max time on the max date and need to do it in one query. This can be in a subquery if needed, but I cannot use any # tables.

Example:
Name Time Date

John 0830 12/10/2008
Paul 1000 12/10/2008
George 1615 12/8/2008
Ringo 1200 12/9/2008

Since Paul had the max time on the max date, I need that record. Max time from the max date in the table.
I tried using a subquery but I'm missing something.

Any ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-16 : 04:17:58

SELECT TOP 1 *
FROM Table1
ORDER BY Date DESC, Time DESC

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 04:35:02

whats datatype of Time field?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-16 : 05:03:39

Just in case date column is varchar.

SELECT TOP 1 *
FROM Table1
ORDER BY CONVERT(DATETIME, Date, 101) DESC, Time DESC

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

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-16 : 05:26:38

declare @Temp Table ( Name varchar(32), Time int, Date varchar(32))
Insert into @temp
select 'John', 0830, '12/10/2008' union all
select 'Paul', 1000, '12/10/2008' union all
select 'George', 1615, '12/8/2008' union all
select 'Ringo', 1200, '12/9/2008'

select top 1 * from @temp order by date,name desc

I Struggle For Excellence

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-16 : 05:28:37

Shouldn't it be the latest (MAX) date?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources