|
View previous topic :: View next topic
|
| Author |
Message |
mijanurit
Currently Banned
New User
Joined: 26 Aug 2005
Posts: 33
Location: bangalore
|
|
|
|
hi experts,
plz tell me the sql query to retrieve second or third top (based on salary)
employee in a org.
regards
mija |
|
| Back to top |
|
 |
aklima
New User
Joined: 25 Nov 2005
Posts: 18
Location: uae
|
|
|
|
hi,
select * from employee where salary =(select max(salary) from employee where salary < (select max(salary) from employee))
i hope this will work for the second max
bye |
|
| Back to top |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL
|
|
|
|
aklima,
| Quote: |
| i hope this will work for the second max |
Yeah, it would.
But this approach can be very tedious while making for big numbers like TOP 25th ....As it needs to cascade Subqueries inturn,
You can give a try to this query ....
| Code: |
SELECT EMP-ID, EMP-NAME, SALARY FROM <TABLE-NAME> A
WHERE N = (SLECT COUNT(*) FROM <TABLE-NAME> B WHERE B.SALARY<A.SALARY); |
Regards,
Priyesh. |
|
| Back to top |
|
|
aklima
New User
Joined: 25 Nov 2005
Posts: 18
Location: uae
|
|
|
|
sure priyesh i shall try out this
bye and thanx |
|
| Back to top |
|
|
cheli
New User
Joined: 21 Jul 2005
Posts: 11
|
|
|
|
Hi...a small correction the query given by Priyesh...
| Code: |
SELECT EMP-ID, EMP-NAME, SALARY FROM <TABLE-NAME> A
WHERE N-1 = (SLECT COUNT(*) FROM <TABLE-NAME> B WHERE B.SALARY<A.SALARY); |
[/code]
Here N gives the no:of Nth maximum as u required.
Bye.. |
|
| Back to top |
|
|
rajesh_1183
Active User
Joined: 24 Nov 2005
Posts: 121
Location: Tadepalligudem
|
|
|
|
Hi,
Can anybody explain the query step by step how it works with an example
Thanks,
Rajesh |
|
| Back to top |
|
|
winchaat
New User
Joined: 23 Mar 2005
Posts: 14
|
|
|
|
A few corrections in above replied answers:
For nth maximum salary query should be like this
SELECT salary FROM HUND7.EMPLOY A WHERE N=(SELECT COUNT (DISTINCT(SALARY)) FROM HUND7.EMPLOY B WHERE A.SALARY<=B.SALARY)
if we missed distinct keyword then duplication rows also counted and give
the result as some other value.
explanation
The above query takes one row from a table and compare with each row based on the salary value and get the count for that which should be equal to value u given. |
|
| Back to top |
|
|
kumar_s
New User
Joined: 15 Mar 2006
Posts: 2
|
|
|
|
| May i know how to find the same, with making use of 'rownumber' funcition. |
|
| Back to top |
|
|
kumar_s
New User
Joined: 15 Mar 2006
Posts: 2
|
|
|
|
| Or may I know the Syntax to use the ROWNUMBER function. |
|
| Back to top |
|
|
|
|
