Have Geek Brain

SQL subquery 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. By nature, there are multiple UserSessionIDs rows in the tblUserSessionSequence. I ONLY want to return the last (first 1) row with a unique UserSessionID. How to do it?

+2


a source to share


2 answers


You can use the windowing ROW_NUMBER function for the row number for each user and only select the 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
+4


a source


If you only want to return one row in your request (i.e. the id with the last timestamp), just change

SELECT

to

SELECT TOP 1


If you want to get one row for each UserSessionID, but you want you to get the last one TimeStamp

, it's a little more complicated.

You can 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
0


a source






More articles:


All Articles