Finding the 3rd highest in the table using SQL.

10 pts.
Tags:
SQL Database
SQL Query
The table name is 'Employee' with the columns Empname Sal Age A 8000 25 B 7000 24 C 6000 26 D 9000 25 what is the syntax used to find the 3rd highest. I have tried with all the syntax but no gain. I have searched in w3schools.com but was not able to find the right answer. Kindly help.... Reks

Answer Wiki

Thanks. We'll let you know when a new response is added.

If you want to look for the third highest SAL, this query could do the trick.

<pre>select * from Employee E
where 2 = (select count(distinct sal) from Employee where sal > E.sal);</pre>

I think it should exist another way to do it, but this was the first thing that came to mind.

——————————————————-

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Kccrosser
    The query above will work for most cases, but will have problems when there are multiple records with the same salary amount. If performance is not a big issue, I would tend to do this with a simple, but brute force approach, as follows:
    select top 1 * from
    (
       select top 3 with ties empname, sal, age      -- get the top 3 salary records
       from Employee     -- get all salary records by salary descending
       order by sal desc, empname desc, age
    ) T
    order by sal, empname, age;/CODE]
    
    Getting the "nth" top salary is just a matter of substituting the "3" with the corresponding "n" value.
    Note that I suggest ordering not just on salary, but on empname and age, so that if some of the top values have the same salary, the list will be returned in the same order every time.
    If there is another column containing any guaranteed unique value (e.g., rowid from Oracle, or an Employee ID value), that would be a better column on which to sort, and the query could be made much more efficient, viz:
    
    
    select E.*
    from
    	Employee E,
    (
    select top 1 * from
    (
       select top 3 with ties empid, sal      -- get the top 3 salary records
       from Employee     -- get all salary records by salary descending
       order by sal desc, empid desc
    ) T
    order by sal, empid
    ) T2
    where E.empid = T2.empid;
    
    This latter is more efficient, in that the intermediate view sort only needs to deal with two columns, and if "empid" is a numeric value, the sort will be more efficient than a text sort. (Note- the "with ties" syntax is required to avoid problems with duplicate rows, although as long as there is some uniqueness in the combination of columns in the query, this isn't a problem. If you could have two people with the same name, salary, and age, then you would need this to ensure you don't lose some rows with the "top" syntax.)
    3,830 pointsBadges:
    report
  • carlosdl
    Well, it depends on how you want to manage those records with the same salary. If you have, for example, the following salaries: 3000,3000,3000,2000,1000,500, etc... When you are asked for the three highest, you could answer: 3000,3000,3000, but someone else could say 3000 (having 3 people with this amount), 2000, 1000. If I ask for the employee with the highest salary, and I get one single name, I would believe that he/she is in fact the employee with the highest salary, but in fact, there are two more employees with the same amount, and that one was picked because of his (her) name. I like your approach, but as I said... it depends. Best regards,
    68,780 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following