Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

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

 
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Velmurugan_j

New User


Joined: 04 Aug 2005
Posts: 49

PostPosted: Tue Dec 27, 2005 1:17 pm    Post subject: How to fetch the fifth Maximum salary from the table in DB2
Reply with quote

Hi

Can anyone tell me

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


Regards,
Velmurugan J
Back to top
View user's profile Send private message

iknow

Active User


Joined: 22 Aug 2005
Posts: 413
Location: Colarado, US

PostPosted: Tue Dec 27, 2005 1:26 pm    Post subject: Re: How to fetch the fifth Maximum salary from the table in
Reply with quote

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
View user's profile Send private message
sunish

New User


Joined: 23 May 2005
Posts: 19

PostPosted: Tue Dec 27, 2005 1:37 pm    Post subject: Re: How to fetch the fifth Maximum salary from the table in
Reply with quote

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
View user's profile Send private message
Velmurugan_j

New User


Joined: 04 Aug 2005
Posts: 49

PostPosted: Tue Dec 27, 2005 2:20 pm    Post subject: Thanks
Reply with quote

Thanks

Its working fine
Back to top
View user's profile Send private message
rajesh_1183

Active User


Joined: 24 Nov 2005
Posts: 121
Location: Tadepalligudem

PostPosted: Wed Jan 04, 2006 12:52 pm    Post subject:
Reply with quote

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
View user's profile Send private message
umeshkmrsh

New User


Joined: 21 Sep 2005
Posts: 79
Location: India

PostPosted: Wed Jan 04, 2006 1:46 pm    Post subject:
Reply with quote

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
View user's profile Send private message
rajesh_1183

Active User


Joined: 24 Nov 2005
Posts: 121
Location: Tadepalligudem

PostPosted: Wed Jan 04, 2006 3:08 pm    Post subject:
Reply with quote

yaa umesh ,

still not clear

can u explain with the above example..

Thanks in adv,
Rajesh.
Back to top
View user's profile Send private message
umeshkmrsh

New User


Joined: 21 Sep 2005
Posts: 79
Location: India

PostPosted: Wed Jan 04, 2006 4:14 pm    Post subject:
Reply with quote

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
View user's profile Send private message
rajesh_1183

Active User


Joined: 24 Nov 2005
Posts: 121
Location: Tadepalligudem

PostPosted: Wed Jan 04, 2006 4:22 pm    Post subject:
Reply with quote

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




Thanks,
Rajesh
Back to top
View user's profile Send private message
umeshkmrsh

New User


Joined: 21 Sep 2005
Posts: 79
Location: India

PostPosted: Wed Jan 04, 2006 4:23 pm    Post subject:
Reply with quote

So its clear to you finally.
icon_biggrin.gif
Back to top
View user's profile Send private message
futuredba

New User


Joined: 08 Jan 2006
Posts: 22
Location: Delhi

PostPosted: Sun Jan 08, 2006 4:23 pm    Post subject: Re: How to fetch the fifth Maximum salary from the table in
Reply with quote

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
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 686
Location: Earth

PostPosted: Fri Oct 19, 2007 10:53 am    Post subject:
Reply with quote

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
View user's profile Send private message
dr_te_z

New User


Joined: 08 Jun 2007
Posts: 71
Location: Zoetermeer, the Netherlands

PostPosted: Fri Oct 19, 2007 11:25 am    Post subject:
Reply with quote

http://www.craigsmullins.com/dbu_0502.htm
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Fri Oct 19, 2007 5:10 pm    Post subject:
Reply with quote

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: http://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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us