My table has 2 columns: Emp_Name and Emp_NR.
It can happen that one Emp_Name value has 2 different Emp_NR values.
I would like to create a SELECT statement that gets only a single value of Emp_Name and Emp_NR
The statement should be something like in Teradata:
SELECT
Emp_Name,
Emp_NR
FROM Table
QUALIFY Row_Number OVER (PARTITION BY Emp_Name ORDER BY Emp__NR DESC) = 1
In addition to that, I would like to get the highest Emp_NR that is assigned to a specific Emp_Name.
2 Answers
You did everything right.
SELECT Emp_NR, Emp_Name
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Emp_Name ORDER BY Emp_NR DESC) AS RN, Emp_Name, Emp_NR
FROM YourTable
) AS T
WHERE T.RN = 1;
This is correct syntax.
Another way is to use ORDER BY combined with TOP 1 WITH TIES:
SELECT TOP 1 WITH TIES Emp_Name, Emp_NR
FROM Table
ORDER BY ROW_NUMBER() OVER (PARTITION BY Emp_Name ORDER BY Emp_NR DESC);
Performance may be slight worse than the solution using subquery.
