Home Forums SQL Server 2008 T-SQL (SS2K8) T-SQL, SELECT 10TH AND 11TH HIGHEST SALARY EMPLOYEES FROM EMPLOYEE TABLE RE: T-SQL, SELECT 10TH AND 11TH HIGHEST SALARY EMPLOYEES FROM EMPLOYEE TABLE

  • Hoping this is late enough not to be useful with your interview but soon enough to help you learn something:

    WITH Salaries AS (

    SELECT

    EmpID,

    EmpLastName,

    EmpFirstName,

    Salary,

    rn = ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Salary DESC)

    FROM

    dbo.Employee)

    SELECT

    *

    FROM

    Salaries

    WHERE

    rn between 10 and 11;

    -- or, without windowing functions ala SQL Server 2000:

    select top 2

    *

    from

    (select top 11

    EmpID,

    EmpLastName,

    EmpFirstName,

    Salary

    from

    dbo.Employee

    order by

    Salary DESC) dt

    order by

    dt.Salary ASC;