HOW TO GET THE 5TH MAX SALARY USING QUERY?

shivakumar .b · New User Joined: 11 May 2005 Posts: 18 Location: bangalore

HI EVERYBODY,
THIS IS B.SHIVAKUMAR.
I KNOW HOW TO GET THE 2ND MAX SALARY BUT I DON'T KNOW HOW TO GET 5TH OR 6TH HIGHEST SALARY.
CAN ANYBODY PLZ TELL ME HOW TO GET THE 5TH MAX SALARY USING QUERY?
THANKS IN ADVANCE.
REGARDS,
B.SHIVAKUMAR

vasanthanc · New User Joined: 01 Apr 2005 Posts: 58

go thru the link below

ibmmainframes.com/viewtopic.php?t=1472

this question has already been answered. kindly search the forum before u post a question.

shivakumar .b · New User Joined: 11 May 2005 Posts: 18 Location: bangalore

hello vasanth, thanks for u r reply.
but still i didnot understand the query for finding Nth maximum salary.
I was not clear about that query, can u xplain me in detail.
thanks in advance.
regards,
b.shivakumar

kkumarv · Posted: Tue May 24, 2005 2:28 pm

SELECT DISTINCT (SALARY)
FROM EMPTABLE A
WHERE 5 = (SELECT COUNT (DISTINCT (B.SALARY)) FROM EMPTABLE B
WHERE A.SALARY <= B.SALARY);

vasanthanc · New User Joined: 01 Apr 2005 Posts: 58

First u shud understand the sub query

SELECT COUNT (DISTINCT (B.SALARY)) FROM EMPTABLE B
WHERE A.SALARY <= B.SALARY

In the sub query we are finding the count of employees in emp table having their salary less than or equal to X. (imagine X as some value. The X is taken from emptable of main query and it will be iterated for each salary value).

Now come to main query. It just finds the salary from employee table when the count from sub query matches N (in ur case 5).

Hope u understand...

jkbytes · Posted: Wed May 25, 2005 10:45 am

QUERY : TO FIND OUT THE Nth HIGHEST SALARY

SELECT EMPSAL FROM EMPNEW E1 WHERE n =
(SELECT COUNT(*) FROM EMPNEW E2
WHERE E1.EMPSAL<=E2.EMPSAL);

I GUESS THIS IS ANOTHER WAY OF GETTING THE Nth SALARY.

Thanks & Regards,

JKBYTES.