Calrify Exercise #1 implementation of row functions
The Exercise #1 question states the following for row_num2
- a column containing the sequential row numbers associated to the rows for each manager, where their highest salary has been given a number equal to the number of rows in the given partition, and their lowest - the number 1.
So row_num2 should have 1 for min salary
i.e. for emp_no 110022 and salary 108407 row_num2 should be 18
Can someone please explain which is wrong, solution provided or my understanding?
MariaDB [employees]> SELECT
->
-> dm.emp_no,
->
-> salary,
->
-> ROW_NUMBER() OVER (PARTITION BY emp_no ORDER BY salary ASC) AS row_num1,
->
-> ROW_NUMBER() OVER (PARTITION BY emp_no ORDER BY salary DESC) AS row_num2
->
-> FROM
->
-> dept_manager dm
->
-> JOIN
->
-> salaries s ON dm.emp_no = s.emp_no;
+--------+--------+----------+----------+
| emp_no | salary | row_num1 | row_num2 |
+--------+--------+----------+----------+
| 110022 | 108407 | 18 | 1 |
| 110022 | 104485 | 17 | 2 |
| 110022 | 100592 | 16 | 3 |
| 110022 | 100014 | 15 | 4 |
| 110022 | 98843 | 14 | 5 |
| 110022 | 97604 | 13 | 6 |
| 110022 | 96647 | 12 | 7 |
| 110022 | 94286 | 11 | 8 |
| 110022 | 92165 | 10 | 9 |
| 110022 | 89204 | 9 | 10 |
| 110022 | 86797 | 8 | 11 |
| 110022 | 82871 | 7 | 12 |
| 110022 | 81784 | 6 | 13 |
| 110022 | 78443 | 5 | 14 |
| 110022 | 76211 | 4 | 15 |
| 110022 | 72970 | 3 | 16 |
| 110022 | 71820 | 2 | 17 |
| 110022 | 71166 | 1 | 18 |
I think the result soultion downloaded has the solutions swapped
Exercise #1 solution is given Solution #2
Exercise #2 solution is given Solution #1
Hi Palachuri!
Thanks for reaching out and pointing this out. You are right - by mistake, we had swapped the two solutions!
We have updated the relevant *.sql solution which you should be able to download and use shortly.
Hope this helps.
Best,
Martin