Row_number function using directly

Question

Is there any direct way of using row_number() function? I want to find 2 nd highest salary

SELECT DISTINCT id
    ,salary
    ,depid
    ,ROW_NUMBER() OVER (
        PARTITION BY depid ORDER BY salary DESC
        ) AS rownum
FROM emp
WHERE rownum = 2;

It gives an error, However the below code works fine.

SELECT *
FROM (
    SELECT DISTINCT id
        ,salary
        ,depid
        ,ROW_NUMBER() OVER (
            PARTITION BY depid ORDER BY salary DESC
            ) AS rownum
    FROM emp
    ) AS t
WHERE t.rownum = 2;

Is any way of directly using the row_number() function as in the first option which is giving the error?

Why do you think the use of DISTINCT is useful here? – SMor Feb 03 '20 at 15:53 — SMor, Feb 03 '20 at 15:53

score 1 · Answer 1 · answered Feb 03 '20 at 16:15

You can not use the alias name of the same query as the condition for the where clause. You also can not use windowed queries as a passing condition in the where clause.

Here is a detailed explanation Why no windowed functions in where clauses?. It is so you need another query outside the inner query and needs to write sub-query.

You can get the Nth highest salary in SQL Server from the below query.

SELECT TOP 1 salary
FROM (
    SELECT DISTINCT TOP N salary
    FROM <YourTableNameHere>
    ORDER BY salary DESC
    ) AS TEMP
ORDER BY salary

VBoka · Answer 2 · 2020-02-04T06:12:25.970

This query will give you the second highest salary ? No

SELECT id
    ,salary
    ,depid
from emp
ORDER BY salary DESC
OFFSET 1 ROWS
FETCH FIRST 1 ROWS ONLY;

Well actually, it will give you the salary that is on the second position when you order the salary's from highest to lowest... So if the highest is 100 and the second highest is 100 then you will get 100 as a result. To conclude this will return a row on the second place depending on the order by clause...

This next query will give you the second highest salary :

SELECT max(id)
      , salary
      , max(depid)
from emp
group by salary
ORDER BY salary DESC
OFFSET 1 ROWS
FETCH FIRST 1 ROWS ONLY;

But be aware, in case you have two employees from two different departments with the same salary then it will return you the one with the higher id and it will return the higher department id which can be incorrect.

And finally this will give you one employee that has a second largest salary with correct data:

SELECT id
      , salary
      , depid
from emp
where id = (SELECT max(id)
            from emp
            group by depid, salary
            ORDER BY salary DESC
            OFFSET 1 ROWS
            FETCH FIRST 1 ROWS ONLY);

Hi @Adi, I have updated my answer(last query). A depid in group by vlause was all you needed. Cheers. — VBoka, Feb 04 '20 at 06:13

LukStorms · Answer 3 · 2020-02-03T16:37:10.160

You could use a variation on the trick that uses a TOP 1 WITH TIES in combination with an ORDER BY ROW_NUMBER

SELECT TOP 1 WITH TIES 
id, 
salary, 
depid
FROM emp 
ORDER BY IIF(2 = ROW_NUMBER() OVER (PARTITION BY depid ORDER BY salary DESC), 1, 2)

But this trick does have the disadvantage that you can't sort it by something else.
Well, not unless you wrap it in a sub-query and sort the outer query.

A test on rextester here

score 0 · Accepted Answer · answered Feb 03 '20 at 16:38

0

First, you want dense_rank(), not row_number() if you want the second highest value -- ties might get in the way otherwise.

You can use an arithmetic trick:

SELECT TOP (1) WITH TIES id, salary, depid
FROM emp
ORDER BY ABS(DENSE_RANK() over (PARTITION BY depid ORDER BY salary DESC) - 2)

The "-2" is an arithmetic trick to put the "second" values highest.

That said, I would stick with the subquery because the intent in clearer.

answered Feb 03 '20 at 16:38

Gordon Linoff

1,242,037
58
646
786

thanks gordon ,we can use any function rank or denserank but looking for alternate ways instead of just using subquery – Adi Feb 04 '20 at 06:07

score 0 · Answer 5 · edited Feb 03 '20 at 19:44

I prefer to use dense_rank() instead of row_number() function with CTE (common table expression) for the scenario you have mentioned. CTE is modern, easy to use and have many cool features like it is memory resident, it can be used for DUI operations, it make code easy to understand etc.

To find Nth highest salary, the CTE look like

;with findnthsalary
as
(
select empid, deptid, salary,
dense_rank() over(partition by deptid order by salary desc) salrank
from
Employee
)
select distinct id, deptid, salary
from findnthsalary
where salrank = N

I used dense_rank() because if you use row_number() it will produce the wrong result in case multiple employees have the same salary in the same department.

Row_number function using directly

5 Answers5