Group By DateTime showing latest date

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

Transact-SQL (2008)

Group By DateTime showing latest date

Author

Topic

darenkov
Yak Posting Veteran

90 Posts

Posted - 2012-04-03 : 17:18:37

I have the following query but I want to display only the latest (newest) date for each user, ie Rick James to only display once with the latest date. My sample code and data is below:

select U.UserID, U.FirstName, U.LastName, U.EmailAddress, SI.StartDateTime, SI.MessageCode from Users U
join SessionInfo SI
on U.UserID = SI.UserID
group by U.UserID, U.FirstName, U.LastName, U.EmailAddress, SI.StartDateTime, SI.MessageCode
order by SI.StartDateTime
DESC

87	Rick	James	rjames@hotmail.com	2012-05-03 01:04:00	0

89	Graeme	Clover	glocver@hotmail.com	2012-04-03 01:04:00	0
88	asds	aasdsa	ads@asd.com	2012-04-03 00:55:00	6
87	Rick	James	rjames@hotmail.com	2012-04-03 00:53:00	0
86	Chris	Riley	criley@gmail.com	2012-04-03 00:38:00	3

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-04-03 : 17:27:38

[CODE]select a.*
from (
select U.UserID, U.FirstName, U.LastName, U.EmailAddress, SI.StartDateTime, SI.MessageCode,
row_number() over(partition by U.UserID order by SI.StartDateTime DESC) rn
from Users U
join SessionInfo SI
on U.UserID = SI.UserID
) a
where a.rn = 1
order by a.StartDateTime DESC[/CODE]

=================================================
There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE)

darenkov
Yak Posting Veteran

90 Posts

Posted - 2012-04-03 : 17:46:28

thank you. nice solution. i must learn more about partitioning

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources