Advanced T-SQL query - SQL Server Forums

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)

Advanced T-SQL query

Author

Topic

tr_sql
Starting Member

2 Posts

Posted - 2012-07-31 : 00:25:38

hi, I am seeking to write a MS SQL Select Statement which involves two tables (A & B). A contains User info and B contains their login info. I want the Select statement to select the two most recent Login times (CreateDateTime column) from the B table for each user and display that in the recordset.

Here is what I've got so far:

SELECT U.UserId, U.FirstName + ' ' + U.LastName AS UserFullName, U2.CreateDateTime AS MostRecentDateTime, COALESCE (CONVERT(VARCHAR, U2.CreateDateTime), 'none') AS SecondMostRecentDateTime
FROM
[User] AS U LEFT OUTER JOIN UserLog AS U2
ON U2.UserId = U.UserId
AND U2.CreateDateTime = (SELECT TOP (1) CreateDateTime
FROM UserLog
WHERE (UserId = U.UserId) AND (EventId = 1)
ORDER BY CreateDateTime DESC)
WHERE (U.UserId IN (SELECT DISTINCT UserId FROM [User]))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-31 : 00:37:25

[code]SELECT UserID, UserFullName, CreateDateTime
FROM
(
SELECT U.UserId, U.FirstName + ' ' + U.LastName AS UserFullName,
U2.CreateDateTime,
RN = ROW_NUMBER() OVER (PARTITION BY U.UserId ORDER BY U2.CreateDateTime DESC)
from [User] AS U
LEFT OUTER JOIN UserLog AS U2 ON U2.UserId = U.UserId
AND U2.EventId = 1
) U
WHERE RN <= 2[/code]

KH
[spoiler]Time is always against us[/spoiler]

tr_sql
Starting Member

2 Posts

Posted - 2012-07-31 : 18:40:00

hi khtan

That result is exactly what I want, except that I am seeking to put the two results in one row, i.e. Output: UserId, UserFullName, MostRecentDateTime, SecondMostRecentDateTime

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources