CODEWITHSUNDEEP
sqlfunctionrow-number
Stephanie
Stephanie

Reputation: 516

Order By in Row_number not working properly

I have ROW_NUMBER function that I am using to get the latest record but the Order by in the function is not seem to be working. Not sure what I am doing wrong.

SELECT *,
ROW_NUMBER() OVER(PARTITION BY EmployeeID, DepartmentID ORDER BY PayDate, WorkDate desc) AS CurrentFlag
FROM test

Example of result table:

EmployeeID | DepartmentID | Balance | PayDate  |  WorkDate   | CurrentFlag
123          1              200       1/3/2017    12/22/2016   1
123          1              300       1/3/2017    12/23/2016   2
123          1              500       1/5/2017    12/24/2016   3

What I should be getting:

EmployeeID | DepartmentID | Balance | PayDate  |  WorkDate   | CurrentFlag
123          1              200       1/3/2017    12/22/2016   3
123          1              300       1/3/2017    12/23/2016   2
123          1              500       1/5/2017    12/24/2016   1

I am not sure what I am doing wrong in my ROW_NUMBER function. I am ordering it by PayDate then WorkDate in descending order so I should get CurrentFlag = 1 for the latest WorkDate which is 12/24/2016. Am I missing something here?

Upvotes: 3

Views: 2107

Answers (1)

Aaron Dietz
Aaron Dietz

Reputation: 10277

Each ORDER BY item is handled separately, and they are considering ASC unless told otherwise.

It appears you want both items to be DESC, so try:

ROW_NUMBER() OVER(PARTITION BY EmployeeID, DepartmentID 
                  ORDER BY PayDate desc, WorkDate desc)

Upvotes: 4

Related Questions