2

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?

Improve this question
2
  • possible duplicate of "Simple" SQL Query Commented May 12, 2010 at 19:34
  • Latest UserSessionID with the higest UserSessionSequenceID Commented May 12, 2010 at 19:40

2 Answers 2

Reset to default
4

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
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Let me specify a bit more. I still want multiple rows in my overall result. Just only want the first unique UserSessionID's from tblUserSessionSequence
Get this error when I try to run it with the code above from you: Msg 1033, Level 15, State 1, Line 21 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
@seo20: Try removing the order by in the subselect and see if that helps. I've updated my answer.
Still getting error: Msg 102, Level 15, State 1, Line 9 Incorrect syntax near ')'. Msg 102, Level 15, State 1, Line 27 Incorrect syntax near 'T1'.
@seo20: There was a missing comma.
|
0

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
Improve this answer

2 Comments

Almost works want UserSessionID with the higest UserSessionSequenceID from tblUserSessionSequence.
@seo20: You can get whatever order you like by altering the ORDER BY clause inside the subselect. I've updated the answer to give what you're looking for.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.