p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Language (http://p2p.wrox.com/forumdisplay.php?f=100)
-   -   Select top third Salary (http://p2p.wrox.com/showthread.php?t=6885)

rmukhija December 4th, 2003 03:56 PM

Select top third Salary
 
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


All times are GMT -4. The time now is 11:25 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.