QUALIFY-Like Function in SQL Server

Question

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.

Answer 1

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.

Answer 2

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.