| Author |
 Topic  |
|
|
gian72
Starting Member
Italy
6 Posts |
 Posted - 01/20/2010 : 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
|
Edited by - gian72 on 01/20/2010 17:18:24
|
|
|
shan
Yak Posting Veteran
USA
79 Posts |
Posted - 01/20/2010 : 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
Italy
6 Posts |
Posted - 01/20/2010 : 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
USA
79 Posts |
Posted - 01/21/2010 : 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
Italy
6 Posts |
Posted - 01/21/2010 : 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
Flowing Fount of Yak Knowledge
USA
1836 Posts |
Posted - 01/21/2010 : 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
Italy
6 Posts |
Posted - 01/21/2010 : 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
Flowing Fount of Yak Knowledge
USA
1836 Posts |
Posted - 01/21/2010 : 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
Flowing Fount of Yak Knowledge
USA
1836 Posts |
Posted - 01/21/2010 : 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
Italy
6 Posts |
Posted - 01/22/2010 : 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
Flowing Fount of Yak Knowledge
USA
1836 Posts |
Posted - 01/22/2010 : 09:38:17
|
| Np. You're welcome. |
|
|
| |
Topic |
|
Select max data, show corresponding row
