SELECT user.id
,user.emp_id
,user.name,COUNT(transfer.record_name) AS current_rank
,ROW_NUMBER() OVER () AS count
FROM transfer AS transfer
LEFT JOIN users AS user ON user.emp_id= transfer.user_id
WHERE transfer.lobs= 0
AND transfer.shift_date = '2020-03-27'
GROUP BY user.emp_id
ORDER BY current_rank DESC
This query will give me a result of like this:
user.id| user.emp_id| user.name| current_rank| count
25 1234 test1 4 4
30 4321 test2 2 2
18 5678 test3 1 1
12 8765 test4 1 3
My goal is to use ORDER BY DESC first so that I can order the current_rank
and the count
column should have the count of:
user.id| user.emp_id| user.name| current_rank| count
25 1234 test1 4 1
30 4321 test2 2 2
18 5678 test3 1 3
12 8765 test4 1 4
How can I use the ROW_NUMBER() function after I ORDER BY my current_rank
column?
Thank you any help. I will appreciate it.
The result of ROW_NUMBER()
is not affected by the sort in the query.
Instead, it accepts an ORDER BY
option within its OVER
clause: without it, the ordering of ROW_NUMBER()
is undefined, meaning that the database is free to order the rows as it likes. You may observe that the same ordering is used over consecutive executions, but the database does not guarantee it.
So, do add an ORDER BY
to the window function:
ROW_NUMBER() OVER (ORDER BY COUNT(transfer.record_name) DESC) AS count