Select top third Salary

rmukhija · December 4th, 2003, 03:56 PM

How can I select the employee who has top third salary from employee table which has Name and sal columns.

Name Sal

Dave 80000
Tom 70000
Dan 55000
John 81000
Jim 48000

Thanks

sal · December 4th, 2003, 04:09 PM

What is the front end? You could select top 3 and then the min of top 3. Use a subquery.

Sal

Jeff Mason · December 4th, 2003, 04:12 PM

Can't quite parse your requirement. Do you mean you want the set of rows whose 'Sal' value is in the top 1/3 of all 'Sal' values? If your database implements the TOP clause (e.g. SQL Server) you could use:

Code:

SELECT TOP 33.3 PERCENT * FROM yourtable ORDER BY Sal DESC;

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

rmukhija · December 4th, 2003, 04:26 PM

Thanks for your quick responses.

Database is Oracle and by top third I meant that, select the name of the person who gets the 3rd highest salary from the table.

pmmgpgp · December 4th, 2003, 06:32 PM

I'd suggest that You use

select rownum = 3

ORDER BY Sal DESC

Dave

Jeff Mason · December 5th, 2003, 08:06 AM

Try this:

Code:

SELECT T0.Name, T0.Sal FROM yourtable T0
WHERE 3 = (SELECT COUNT(DISTINCT Sal) FROM yourtable T1
        WHERE T1.Sal>=T0.Sal)

The (correlated) subquery assigns a ranking number to each row based on the 'Sal' column. The WHERE clause finds the row with the ranking number equal to 3. Note that this deals with ties by assigning the same ranking number to each row with the same value, so you could get more than one row in the resultset.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

rmukhija · December 5th, 2003, 10:59 AM

Thanks, This works like you described.

owain · December 18th, 2003, 05:32 AM

Another way of doing it would be to use a derived table. The inner query selects the top 3 salaries and the outer query selects the lowest salary from this result set.

Code:

  SELECT TOP 1 [Name], Sal
    FROM (SELECT TOP 3 [Name], Sal
            FROM employee
        ORDER BY Sal DESC) AS E2
ORDER BY Sal

This will return only one row, and that row will be the third row in the table if you had selected it by returning all the rows, ordered by salary and moved to the third row. Jeff's query is probably closer to what you want but it shows that there are usually more than one way do achieving what you want in SQL.

Regards
Owain Williams