View previous topic :: View next topic
|
Author |
Message |
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 |
|
Back to top |
|
|
iknow
Active User
Joined: 22 Aug 2005 Posts: 411 Location: Colarado, US
|
|
|
|
Hi,
Please serach our forum. There is a code to find the Nth maximum salary. Put N=5 in your case.
Hope this helps. |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
Velmurugan_j
New User
Joined: 04 Aug 2005 Posts: 49
|
|
|
|
Thanks
Its working fine |
|
Back to top |
|
|
rajesh_1183
Active User
Joined: 24 Nov 2005 Posts: 121 Location: Tadepalligudem
|
|
|
|
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 |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
rajesh_1183
Active User
Joined: 24 Nov 2005 Posts: 121 Location: Tadepalligudem
|
|
|
|
yaa umesh ,
still not clear
can u explain with the above example..
Thanks in adv,
Rajesh. |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
rajesh_1183
Active User
Joined: 24 Nov 2005 Posts: 121 Location: Tadepalligudem
|
|
|
|
Thanks..Thanks..Thanks..Thanks..Thanks..Thanks..Thanks..Thanks..
Thanks,
Rajesh |
|
Back to top |
|
|
umeshkmrsh
New User
Joined: 21 Sep 2005 Posts: 79 Location: India
|
|
|
|
So its clear to you finally.
|
|
Back to top |
|
|
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... |
|
Back to top |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007 Posts: 691 Location: Earth
|
|
|
|
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. |
|
Back to top |
|
|
dr_te_z
New User
Joined: 08 Jun 2007 Posts: 71 Location: Zoetermeer, the Netherlands
|
|
Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
|
|
|
|
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.
Code: |
SELECT MIN(SALARY)
FROM (SELECT SALARY
FROM TABLE
ORDER BY SALARY
FETCH FIRST 4 ROWS ONLY)
WITH UR
|
|
|
Back to top |
|
|
|