CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
arjit
arjit

Reputation: 11

Query top 2 salaries in each department

Query:

select *, row_number() over (partition by b.[Dept Name] order by a.salary) as row_rank
from dbo.d as a
inner join dbo.e as b on a.[Dept id] = b.[Dept id]
row_rank < 3

Error:

Msg 102, Level 15, State 1, Line 7 Incorrect syntax near 'row_rank'.

I wish to query the following columns:

Upvotes: 0

Views: 571

Answers (1)

Dale K
Dale K

Reputation: 27202

You can't reference a calculated column directly in the where clause. Use a CTE or sub-query.

with cte as (
    select a.Name, a.salary a.[Dept id], b.Dept_Name
        , row_number() over (partition by b.[Dept Name] order by a.salary) as row_rank
    from dbo.d as a
    inner join dbo.e as b on a.[Dept id] = b.[Dept id]
)
select *
from cte
where row_rank < 3;

You don't need [dept id] from both tables as they are the same. Also its advised to alias your tables with a meaningful alias, not just a, b etc

Upvotes: 1

Related Questions