CODEWITHSUNDEEP
sql-serversql-server-2005common-table-expressionrow-number
tsdexter
tsdexter

Reputation: 2991

Is it possible to use ROW_NUMBER() OVER/PARTITION BY only when another column is also the same?

I am using this code: (from this question: How to get the last record per group in SQL substituting my own columns)

WITH e AS
(
 SELECT *,
     ROW_NUMBER() OVER
     (
         PARTITION BY ApplicationId
         ORDER BY theDate DESC
     ) AS Recency
 FROM [Event]
)
SELECT *
FROM e
WHERE Recency = 1

Is it possible to 'partition' only if two fields are the same? For example I have data like this:

ID      Name    theDate
123     John    01/01/2012
123     John    01/02/2012
123     Doe     01/01/2012
456     Smith   02/04/2012
789     Smith   02/01/2012
789     Smith   02/09/2012
789     Roger   02/08/2012

From that data I'd want to return:

ID      Name    theDate
123     John    01/02/2012
123     Doe     01/01/2012
456     Smith   02/04/2012
789     Smith   02/09/2012
789     Roger   02/08/2012

Thanks for any help.

Thomas

Upvotes: 22

Views: 125874

Answers (2)

tsdexter
tsdexter

Reputation: 2991

I've found it the answer here: Table partitioning using 2 columns

You can only partition on 1 column, however that column can be generated to make a 'multiple partition' like so: 

WITH e AS 
( 
 SELECT *, 
 ROW_NUMBER() OVER 
 ( 
     PARTITION BY CONVERT(VARCHAR(100),ApplicationId) + ' ' + Name
     ORDER BY theDate DESC 
 ) AS Recency 
 FROM [Event] 
) 
SELECT * 
FROM e 
WHERE Recency = 1

Adding the two columns together as one single string ensures it will only partition if both columns are identical.

Upvotes: 5

JeffO
JeffO

Reputation: 8043

You can have several columns separated by a comma

WITH e AS 
( 
 SELECT *, 
     ROW_NUMBER() OVER 
     ( 
         PARTITION BY ApplicationId , Name
         ORDER BY theDate DESC 
     ) AS Recency 
 FROM [Event] 
) 
SELECT * 
FROM e 
WHERE Recency = 1

Upvotes: 50

Related Questions