SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select max data, show corresponding row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gian72
Starting Member

Italy
6 Posts

Posted - 01/20/2010 :  17:14:48  Show Profile  Reply with Quote
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


Edited by - gian72 on 01/20/2010 17:18:24

shan
Yak Posting Veteran

USA
84 Posts

Posted - 01/20/2010 :  17:29:43  Show Profile  Reply with Quote
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
Go to Top of Page

gian72
Starting Member

Italy
6 Posts

Posted - 01/20/2010 :  18:15:08  Show Profile  Reply with Quote
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
Go to Top of Page

shan
Yak Posting Veteran

USA
84 Posts

Posted - 01/21/2010 :  09:05:27  Show Profile  Reply with Quote
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
Go to Top of Page

gian72
Starting Member

Italy
6 Posts

Posted - 01/21/2010 :  14:32:29  Show Profile  Reply with Quote
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
Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 01/21/2010 :  15:01:53  Show Profile  Reply with Quote
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
Go to Top of Page

gian72
Starting Member

Italy
6 Posts

Posted - 01/21/2010 :  15:30:51  Show Profile  Reply with Quote
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
Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 01/21/2010 :  15:52:44  Show Profile  Reply with Quote
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]
Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 01/21/2010 :  15:55:09  Show Profile  Reply with Quote
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
Go to Top of Page

gian72
Starting Member

Italy
6 Posts

Posted - 01/22/2010 :  02:43:58  Show Profile  Reply with Quote
Perfect, vijayisonly!
I've tried it and got the right data now.
Thanks to all for your support. Have a nice WE, Gianluca
Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 01/22/2010 :  09:38:17  Show Profile  Reply with Quote
Np. You're welcome.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000