CODEWITHSUNDEEP
sqlsql-server
Lonli-Lokli
Lonli-Lokli

Reputation: 3784

How to do GroupBy and Rank in one sql?

I have history table Date (datetime), Event (varchar), User (varchar), Text (varchar)

Event is a type of event (Exception, Request and so on) of history item, user is user identifier. I want to choose specific event type ('Exception') and get the last Text (by Date) per each user.

Upvotes: 1

Views: 33

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460298

From SQL-Server 2005 on you can use a CTE with ROW_NUMBER:

WITH CTE AS
(
   SELECT [Date], [Event], [User], [Text], 
          rn = ROW_NUMBER() OVER (PARTITION BY [User] ORDER BY [Date] DESC)
   FROM dbo.TableName
   WHERE [Event] = 'Exception'
)
SELECT [Date], [Event], [User], [Text]
FROM CTE
WHERE rn = 1

Upvotes: 2

heikkim
heikkim

Reputation: 2975

SELECT
    T1.[Date],
    T1.Event,
    T1.[User],
    T1.Text
FROM (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY H.[User] ORDER BY H.[Date] DESC) AS ROW_NUM,
        H.[Date],
        H.Event,
        H.[User],
        H.Text
    FROM
        history H
    WHERE
        H.Event = 'Exception'
) AS T1
WHERE 
    T1.ROW_NUM = 1

Upvotes: 0

Related Questions