CODEWITHSUNDEEP
sqlt-sql
user3219693
user3219693

Reputation: 201

Row_Number() Over Partition - Easier Way

I have the below query:

INSERT into @TEST (col1,staffnumber,rn)
SELECT starttime as col1,staffnumber,ROW_NUMBER() OVER (PARTITION BY StaffNumber ORDER BY STARTTIME DESC) AS rn
  FROM @TempTable2 t2
UNION 
SELECT eventdate as col1, staffnumber,ROW_NUMBER() OVER (PARTITION BY StaffNumber ORDER BY EVENTDATE DESC) AS rn
  FROM @TempTable1 t1

But, obviously, this gives me the following values:

COL1                | staffnumber    | rn
10/02/2014 10:29:24         1          1
10/02/2014 09:08:19         1          3
10/02/2014 10:00:23         1          2
10/02/2014 10:00:29         1          1

Whereas, I need it to show:

COL1                | staffnumber    | rn
10/02/2014 10:29:24         1          1
10/02/2014 09:08:19         1          4
10/02/2014 10:00:23         1          3
10/02/2014 10:00:29         1          2

I can do this by doing the following:

INSERT into @TEST1 (col1,staffnumber,rn)
SELECT convert(time,col1),staffnumber,ROW_NUMBER() OVER (PARTITION BY StaffNumber ORDER BY convert(time,COL1) DESC) AS rn
FROM @TEST

But there must be an easier more efficient way of doing it. Any ideas?

Upvotes: 2

Views: 2657

Answers (1)

usr
usr

Reputation: 171178

Compute the ROW_NUMBER after the UNION.

SELECT *, ROW_NUMBER() OVER (PARTITION BY StaffNumber ORDER BY STARTTIME DESC) AS rn
FROM (
 SELECT * FROM T1
 UNION ALL
 SELECT * FROM T2
) x

I suspect you need UNION ALL.

Upvotes: 4

Related Questions