How to fetch the fifth Maximum salary from the table in DB2

Velmurugan_j · New User Joined: 04 Aug 2005 Posts: 49

Hi

Can anyone tell me

How to fetch the fifth Maximum salary from the table in DB2

Regards,
Velmurugan J

iknow · Posted: Tue Dec 27, 2005 1:26 pm

Hi,

Please serach our forum. There is a code to find the Nth maximum salary. Put N=5 in your case.

Hope this helps.

sunish · New User Joined: 23 May 2005 Posts: 19

hi

select sal from tabl1 a where 5=(select count(*) from tabl1 b where b.sal < a.sal) this will work . here a and b is like a alias name of your tabl1. check it if it is not working pl let me know.

Velmurugan_j · New User Joined: 04 Aug 2005 Posts: 49

Thanks

Its working fine

rajesh_1183 · Posted: Wed Jan 04, 2006 12:52 pm

Hi sunish,

can u explain me the query with an example...like..

Salary
--------
10000
20000
30000
40000
50000
60000
70000
80000

Thanks in adv,
Rajesh

umeshkmrsh · New User Joined: 21 Sep 2005 Posts: 79 Location: India

The query given by Sunish uses a corelated sub-query. His query is:

SELECT A.*, A.SALARY FROM SALARY-TB A WHERE 5=(SELECT COUNT(*)FROM SALARY-TB B WHERE B.SALARY < A.SALARY);

This query will fetch only the SALARY ROW for which there are only five rows having lesser salary in SALARY-TBL. For each row of outer query the inner query (sub-query) will get the count of rows having lesser salary.

Let know if still not clear.

rajesh_1183 · Posted: Wed Jan 04, 2006 3:08 pm

yaa umesh ,

still not clear

can u explain with the above example..

Thanks in adv,
Rajesh.

umeshkmrsh · New User Joined: 21 Sep 2005 Posts: 79 Location: India

Ok,

Lets take your example:
Salary
--------
10000
20000
30000
40000
50000
60000
70000
80000

The query will process each row one at a time. ok.

Procession first row: Sal = 10000
The query gets modified to:
SELECT A.SALARY FROM SALARY-TBL A WHERE 5 = (SELECT COUNT(*) FROM SALARY-TBL B WHERE B.SALARY < 10000);

Now the sub query
SELECT COUNT(*) FROM SALARY-TBL B WHERE B.SALARY < 10000);
will result 0 as Count(*) as no row has salary lower than 10000.

Now our main query get modified to:
SELECT A.SALARY FROM SALARY-TBL A WHERE 5 = 0;

As the where clause of this query is FALSE. The first row with 10000 dosen't gets selected.

Similarly
Procession for second row: Sal = 20000
The query gets modified to:
SELECT A.SALARY FROM SALARY-TBL A WHERE 5 = (SELECT COUNT(*) FROM SALARY-TBL B WHERE B.SALARY < 20000);

Now the sub query
SELECT COUNT(*) FROM SALARY-TBL B WHERE B.SALARY < 20000);
will result 1 as Count(*) as only one row has salary lower than 20000.

Now our main query get modified to:
SELECT A.SALARY FROM SALARY-TBL A WHERE 5 = 1;

As the where clause of this query is FALSE. The second row with 20000 dosen't gets selected.

You can try the same for other rows.

Now take 6th row one with salary 60000.

The query gets modified to:
SELECT A.SALARY FROM SALARY-TBL A WHERE 5 = (SELECT COUNT(*) FROM SALARY-TBL B WHERE B.SALARY < 60000);

Now the sub query
SELECT COUNT(*) FROM SALARY-TBL B WHERE B.SALARY < 60000);
will result 5 as Count(*) as five row have salary lower than 60000.

Now our main query get modified to:
SELECT A.SALARY FROM SALARY-TBL A WHERE 5 = 5;

As the where clause of this query is TRUE. This row get selected.

So only sixth row will satisfy the query condition. That will give us the sixth maximum salary.

rajesh_1183 · Posted: Wed Jan 04, 2006 4:22 pm

Thanks..Thanks..Thanks..Thanks..Thanks..Thanks..Thanks..Thanks..

Thanks,
Rajesh

umeshkmrsh · New User Joined: 21 Sep 2005 Posts: 79 Location: India

So its clear to you finally.

futuredba · New User Joined: 08 Jan 2006 Posts: 22 Location: Delhi

Hi Frds,

I think there is a flaw in the query specified above...

Our aim was to get the fiveth highest salary which is 40000, but i think the above query is giving 6th least or 3rd highest sal(according to the table above).

the query should be like this: SELECT A.SALARY FROM SALARY-TB A WHERE 5=(SELECT COUNT(*)FROM SALARY-TB B WHERE B.SALARY >= A.SALARY);

I do have one more doubt regarding this query, this will work fine as long as your emp table has distinct sal, like the table mentioned above but we can have 2 or more emps with the same sal. At that point, this query will fail...

Now consider this table whose salary col is as follows:-

Salary
--------
10000
20000
30000
40000
40000
50000
60000
60000
70000
80000
80000
80000

In this table also, the 5th highest sal is 40000, but the above query will give us 60000 and that too it will give two times coz both the 60000 will satisfy the cond.

now at this stage, all we need to do is to modify the table so that it contain only distinct sal. there are many ways of doing it, one of them is to create a view. Now we can try this...

WITH T1 AS (SELECT DISTINCT SALARY FROM SALARY-TB) SELECT A.SALARY FROM T1 A WHERE 5=(SELECT COUNT(*)FROM T1 B WHERE B.SALARY >= A.SALARY);

Tell me if I missed something or wrong anywhere...

agkshirsagar · Posted: Fri Oct 19, 2007 10:53 am

Good Catch futuredba, last query was giving fifthe lowest salary from the table.
However we must give credit to umesh for good explantaion about correlated subquery.

dr_te_z · Posted: Fri Oct 19, 2007 11:25 am

www.craigsmullins.com/dbu_0502.htm

stodolas · Posted: Fri Oct 19, 2007 5:10 pm

He states that the query given can be quite in efficient.

"It can be quite inefficient when the number of rows grows to as little as a thousand. For very small amounts of data though, this query usually performs quite well. "

In this post: ibmmainframes.com/viewtopic.php?p=102174#102174
I give the following solution. It doesn't perform the correlated query for each row. According to the link given, this is a more "elegant" solution.