Select max data, show corresponding row

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

General SQL Server Forums

New to SQL Server Programming

Select max data, show corresponding row

Author

Topic

gian72
Starting Member

6 Posts

Posted - 2010-01-20 : 17:14:48

Hi here my simple (I hope) question. I' using SQL2000
This is my table

user date time client
user1 05/01/10 11:00 pc0100
user1 08/01/10 12:00 pc0100
user1 08/01/10 13:00 pc0100
user2 03/01/10 15:00 pc0101
user2 08/01/10 11:00 pc0102

now what I need is a select that finds the max in column date and if there are two or more equals dates, the max in column time.
So the result should be for the above example should be:

user date time client
user1 08/01/10 13:00 pc0100
user2 08/01/10 11:00 pc0102

thanks for your time and any helps appreciated.

Regards, Gianluca

shan
Yak Posting Veteran

84 Posts

Posted - 2010-01-20 : 17:29:43

try this

declare @temp_tbl table(usr varchar(10),ts datetime, client varchar(10))

declare @a int

insert into @temp_tbl
select 'user1','05/01/10 11:00','pc0100'
union all
select 'user1', '08/01/10 12:00', 'pc0100'
union all
select 'user1', '08/01/10 13:00', 'pc0100'
union all
select 'user2', '03/01/10 15:00', 'pc0101'
union all
select 'user2', '08/01/10 11:00', 'pc0102'

select a.* from @temp_tbl a
inner join
(
select usr,max(ts) as ts from @temp_tbl group by usr
) b on a.usr = b.usr and a.ts=b.ts

-Shan

gian72
Starting Member

6 Posts

Posted - 2010-01-20 : 18:15:08

Tried, but I've a table with 2 differnet rows for time AND date,perhaps it was not clear in the previous post. That is the difficult part... :)

Thanks, Gianluca

shan
Yak Posting Veteran

84 Posts

Posted - 2010-01-21 : 09:05:27

Not sure what do you mean, the above script will give you the output you asked for.
What output you get and what output you are expecting?

-Shan

gian72
Starting Member

6 Posts

Posted - 2010-01-21 : 14:32:29

Thanks for the quick reply.
As for the example you gave I've modified it because I've 2 columns une for date and one for time:

SELECT a.*
FROM dbo.table a INNER JOIN
(SELECT user, MAX(date) AS date, MAX(time) AS time
FROM table GROUP BY user) b ON a.user = b.user AND a.date = b.date AND a.time = b.time

But what I get is gor each user the max for the date and the max for the time.
So in the example table I gave you I got:
user1 08/01/10 13:00 pc0100
user2 08/01/10 15:00 pc0102

As you see user 2 has the correct max date but the time is from another row, it is a mix from two rows :)

any help is appreciated.

Gianluca

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-21 : 15:01:53

You have date and time in two different columns??? WHy?
Anyways..if you are using SQL 2005 or above..you can try this..

select * from 
(
select row_number() over(partition by user order by date desc, time desc) as seq, * from table
) t
where t.seq = 1

gian72
Starting Member

6 Posts

Posted - 2010-01-21 : 15:30:51

unfortunatly sql2000. I tried your advice but it gives me errors in sql 2000

If the problem is date and time in two colums I can get this unified in one, but the problem will still occur in the column "client"...

Thanks, Gianluca

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-21 : 15:52:44

Well..not the prettiest...but give it a shot.
Assumption : your date and time fields are varchars

select a.*
from table1 a inner join
(
select [user],max(convert(datetime,date+ ' ' + [time])) as [dt]
from table1
group by [user]
) b
on a.[user] = b.[user] and convert(datetime,a.date+ ' ' + a.[time]) = b.[dt]

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-21 : 15:55:09

Here's the data I worked with

declare @t table([user] varchar(5), date varchar(10), [time] varchar(10), [client] varchar(10))
insert @t
select 'user1', '05/01/10', '11:00', 'pc0100'
union all select 'user1','08/01/10', '12:00', 'pc0100'
union all select 'user1', '08/01/10', '13:00', 'pc0100'
union all select 'user2', '03/01/10', '15:00' ,'pc0101'
union all select 'user2', '08/01/10', '11:00' ,'pc0102'

Result

user  date       time       client
----- ---------- ---------- ----------
user1 08/01/10   13:00      pc0100
user2 08/01/10   11:00      pc0102

gian72
Starting Member

6 Posts

Posted - 2010-01-22 : 02:43:58

Perfect, vijayisonly!
I've tried it and got the right data now.
Thanks to all for your support. Have a nice WE, Gianluca

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-22 : 09:38:17

Np. You're welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources