SQL subquery question

Question

I have the following SQL

SELECT
    Seq.UserSessionSequenceID, 
    Usr.SessionGuid, 
    Usr.UserSessionID,
    Usr.SiteID, 
    Seq.Timestamp, 
    Seq.UrlTitle, 
    Seq.Url
FROM
    tblUserSession Usr
INNER JOIN  
    tblUserSessionSequence Seq ON Usr.UserSessionID = Seq.UserSessionID
WHERE     
    (Usr.Timestamp > DATEADD(mi, -45, GETDATE())) AND (Usr.SiteID = 15)
ORDER BY Usr.Timestamp DESC

Pretty simple stuff. There are by nature multiple UserSessionIDs rows in tblUserSessionSequence. I ONLY want to return the latest (top 1) row with unique UserSessionID. How do I do that?

Answer 1

You can use the windowing function ROW_NUMBER to number the rows for each user and select only those rows that have row number 1.

SELECT
    UserSessionSequenceID, 
    SessionGuid, 
    UserSessionID,
    SiteID, 
    Timestamp, 
    UrlTitle, 
    Url
FROM (
    SELECT
        Seq.UserSessionSequenceID, 
        Usr.SessionGuid, 
        Usr.UserSessionID,
        Usr.SiteID, 
        Usr.Timestamp AS UsrTimestamp, 
        Seq.Timestamp, 
        Seq.UrlTitle, 
        Seq.Url,
        ROW_NUMBER() OVER (PARTITION BY Usr.UserSessionID
                           ORDER BY Seq.UserSessionSequenceID DESC) AS rn
    FROM
        tblUserSession Usr
    INNER JOIN  
        tblUserSessionSequence Seq ON Usr.UserSessionID = Seq.UserSessionID
    WHERE     
        (Usr.Timestamp > DATEADD(mi, -45, GETDATE())) AND (Usr.SiteID = 15)
) T1
WHERE rn = 1
ORDER BY UsrTimestamp DESC

Answer 2

If you're looking to return only a single row in your query (i.e., the ID with the latest timestamp), just change

SELECT

to

SELECT TOP 1

If you're looking to obtain a single row for each UserSessionID, but you want to ensure that you get the one with the latest TimeStamp, that's slightly more complex.

You could do something like this:

SELECT  
    Seq.UserSessionSequenceID,   
    Usr.SessionGuid,   
    Usr.UserSessionID,  
    Usr.SiteID,   
    Seq.Timestamp,   
    Seq.UrlTitle,   
    Seq.Url  
FROM  
    tblUserSession Usr  
INNER JOIN    
    (SELECT 
        UserSessionSequenceID, 
        UserSessionID, 
        Timestamp, 
        UrlTitle, 
        Url, 
        ROW_NUMBER() over (PARTITION BY UserSessionID ORDER BY UserSessionSequenceID) AS nbr

    FROM tblUserSessionSequence) Seq ON Usr.UserSessionID = Seq.UserSessionID AND Seq.nbr = 0
WHERE       
    (Usr.Timestamp > DATEADD(mi, -45, GETDATE())) AND (Usr.SiteID = 15)  
ORDER BY Usr.Timestamp DESC