You must Sign In to post a response.
  • Category: SQL Server

    TOP and Distinct Together

    I have one table with salary details against empId.
    Columns like, (Id, EmpId, SalaryPeriod, GrossSalary).

    I have 100 employees and as per the 12 months, i have 12 records per employee.
    Total records = 1200

    I want to write a query by using I get all employee's nth salary (order by).
    So, I can get 100 rows as a result with EmpId and top nth salary in second column.
    Please help me to write this query.

    Also, consider one more scenario which is optional...
    Employees can have same salary for all the 12 months and If I want top 2nd salary, what will be the result.?
  • #765316
    Hi Prajnal,

    As per my understand the post you need to get the Nth salary of all the employee.

    ;WITH salary AS
    SELECT EmpId, GrossSalary,
    DENSE_RANK() OVER(ORDER BY GrossSalaryDESC) AS 'SalaryRank'
    FROM dbo.Employee

    SELECT EmpId, GrossSalary
    FROM salary
    WHERE SalaryRank=1

    Try like this...

    Give respect to your work, Instead of trying to impress your boss.

    Blog :

Sign In to post your comments