CODEWITHSUNDEEP
sqlrow-numbersql-order-by
Edamame
Edamame

Reputation: 25366

SQL: SyntaxError: order by with row_number() function

I have the following table Employee:

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 150    |
| 2  | 290    |
| 3  | 302    |
+----+--------+

I am using the following code to find the second highest salary:

with t as 
(
    select 
        Salary, 
        row_number() over (order by Salary desc) as salary_ord 
    from 
        Employee
)
select Salary 
from t 
where salary_ord == 2

However, I get an error:

SyntaxError: near 't as (
select Salary, row_number() over (order by Salary desc) as salary_ord'

What did I do wrong here? Thanks!

Upvotes: 0

Views: 1086

Answers (2)

Anthony McGrath
Anthony McGrath

Reputation: 802

In SQL Server,

You can do:

select top 1 Salary 
from Employee 
order by Salary desc
offset 1 row fetch next 1 row only

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

In SQL, the correct comparison operator is =, not ==. So, this is the ANSI SQL version of your query:

with t as (
      select Salary, row_number() over (order by Salary desc) as salary_ord 
      from Employee
     )
select Salary
from t
where salary_ord = 2;

However, your error suggests that your database doesn't support with or window functions.

Upvotes: 2

Related Questions