Sort record by date and group it by something

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 2000 Forums

Transact-SQL (2000)

Sort record by date and group it by something

Author

Topic

orga_laut
Starting Member

3 Posts

Posted - 2007-11-15 : 01:29:04

Hi guys,

im new here. while im googling around the net to find a solution, im glad it brings me here, so i thought u guys can help me out.

I have table called Table1
The fields are logindate,loginid,remarks.
Datatype for logindate is in datetime. (ex: 2007-11-15 06:16:22.767)
The data from that table look like this:

logindate | loginid | remarks
------------------------------------------------------
2007-11-15 06:16:42.767 | chan | updated
2007-09-12 06:16:22.312 | chan | pending
2007-09-11 01:46:19.134 | jane | noresponse
2007-07-16 06:16:31.602 | jane | pending
2007-03-15 08:16:22.465 | mary | done

Issit possible to sort the above record by the most recent or the latest logindate, and group it by loginid?

I only want to display logindate and loginid, like this:

logindate | loginid
-----------------------------------------
2007-11-15 06:16:42.767 | chan
2007-09-11 01:46:19.134 | jane
2007-03-15 08:16:22.465 | mary

Many thanks!

=knowledge is power=

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2007-11-15 : 01:41:12

[code]
select max(tbl.logindate), tbl.loginid
from (

select '2007-11-15 06:16:42.767' as logindate, 'chan' as loginid, 'updated' as remarks union all
select '2007-09-12 06:16:22.312' as logindate, 'chan' as loginid, 'pending' as remarks union all
select '2007-09-11 01:46:19.134' as logindate, 'jane' as loginid, 'noresponse' as remarks union all
select '2007-07-16 06:16:31.602' as logindate, 'jane' as loginid, ' pending' as remarks union all
select '2007-03-15 08:16:22.465' as logindate, 'mary' as loginid, 'done' as remarks

) as tbl
group by tbl.loginid
[/code]

Want Philippines to become 1st World COuntry? Go for World War 3...

orga_laut
Starting Member

3 Posts

Posted - 2007-11-15 : 02:08:21

thanx jonasalbert20,.. but the table contains thousands of records.

=knowledge is power=

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-15 : 03:55:37

quote:

orga_laut

thanx jonasalbert20,.. but the table contains thousands of records.

?? the select statement jonasalbert put into that query was just dummying up your data. just swap it for you table...


select  max(logindate), loginid 
from table1
group by loginid

orga_laut
Starting Member

3 Posts

Posted - 2007-11-16 : 02:21:46

yes it works!

thanks a bunch!

=knowledge is power=

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources