I am just wondering if there is any way to select the last row based on ROW_NUMBER() function? Basically I would like to select for each department_id THE LAST (max one) emp_id.
SELECT
department_id, last_name, employee_id,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id
FROM
employees;
Output:
DEPARTMENT_ID LAST_NAME EMPLOYEE_ID EMP_ID
------------- ------------------------- ----------- ----------
10 Whalen 200 1
20 Hartstein 201 1
20 Fay 202 2
30 Raphaely 114 1
30 Khoo 115 2
30 Baida 116 3
30 Tobias 117 4
30 Himuro 118 5
30 Colmenares 119 6
40 Mavris 203 1
. . .
100 Popp 113 6
110 Higgins 205 1
110 Gietz 206 2
Yes, you could add descending order and then filter using that column:
SELECT *
FROM (
SELECT department_id, last_name, employee_id,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_id DESC) l_emp_id
FROM employees
)
WHERE l_emp_id = 1