CODEWITHSUNDEEP
sqlsql-serverssms
Chuck0185
Chuck0185

Reputation: 531

SQL Subquery to Return Nth Highest Salary along with Name, City, etc

Apologies for posting a question similar to one that has been asked many times. I could not find another thread that would return other fields along with the salary field. My SQL is below, I just replace the 3 with whichever number I am looking or. However, I am not sure how to also return the Name, Age, and City fields from tblPerson. I know there needs to be a subquery of some sort but I have not been able to figure it out. Any help would be much appreciated. Thank you in advance!

Select TOP 1 Salary 
FROM
    (Select DISTINCT TOP 3 Salary
     FROM tblPerson
     Order By Salary DESC) Result
ORDER BY Salary

Upvotes: 0

Views: 463

Answers (3)

L Robin
L Robin

Reputation: 55

I see other answers using the select top approach. I would be more inclined to this, but just personal preference. 

SELECT * FROM (
    SELECT *
    ,Row_Number() OVER(ORDER BY Salary DESC) AS rown
    FROM tblPerson
) AS x WHERE rown = '3'

The row number is assigned to each row in the table, ordered by salary. You can then use the where to pick the nth row, in this case salary.

To get a list of all records which match an nth salary:

SELECT * FROM tblPerson as p
INNER JOIN (
    SELECT Salary FROM(
        SELECT Salary
        ,Row_Number() OVER(ORDER BY Salary DESC) AS rown
        FROM tblPerson
    ) AS x1 WHERE rown = '3'
) AS x2 on x2.salary = p.salary

Upvotes: 0

OLIVER.KOO
OLIVER.KOO

Reputation: 5993

simply add what you want the subquery to return into the select clause to have the columns you want to be included in the result table. But also remember to include these columns on the outer query so they appear in your final result. like such:

Select TOP 3 result.salary, result.name, result.age, result.city
FROM
     (Select DISTINCT TOP 3 tblPerson.salary, tblPerson.name, 
      tblPerson.age,tblPerson.city
      FROM tblPerson
      Order By Salary DESC) result
ORDER BY Salary

your result table from your subquery looks like this: 

salary  name       age  city
90000   Mr.derp    21   Minneapolis
80000   Ms.herp    52   St.Paul
70000   Mr.blah    89   Fakecity

It has 3 rows cause of the keyword TOP 3 and the columns as the result of tblPerson.salary, tblPerson.name, tblPerson.age,tblPerson.city in the select clause. Then we can simply select from this table with the outer query.

You can use select * if you want every columns or specify whichever column you want with the column name. You can change n in TOP n to see how many rows you want to select from the top.

Upvotes: 1

Prabhat G
Prabhat G

Reputation: 3029

Didn't try it on machine but let me know result/error of this :

Select TOP 1 a.Salary, a.Name, a.Age, a.City 
FROM
tblPerson a
inner join
 (Select DISTINCT TOP 3 Salary
  FROM tblPerson
  Order By Salary DESC
 )Result
on a.Salary = Result.Salary
ORDER BY a.Salary

Upvotes: 0

Related Questions