MVSFORUMS.com

kolusu · Last edited by kolusu on Wed Oct 29, 2003 10:05 pm; edited 1 time in total

Q.I have an 'employee' table with 3 columns emp-id, emp-name and emp-salary. I want to select first 3 employees in the order of their salary.The trick is to get the results without using a cursor and with a 'single' query.

Answer:

vallishar · Posted: Mon Feb 10, 2003 12:32 pm Post subject:

Hi

Im doubtful of the order of execution in the first query. Could you pls explain?
_________________
If you're not failing every now and again, it's a sign you're not doing anything very innovative.

gotluru · Beginner Joined: 17 Dec 2002 Posts: 29 Topics: 6

Hi Kolusu,

Did this select works for your question?

SELECT *
FROM
EMP_TABLE X
ORDER BY X.SAL DESC
FETCH FIRST 3 ROWS ONLY;

Prasad

vallishar · Posted: Mon Feb 10, 2003 2:50 pm Post subject:

The Query with FIRST FETCH N ROWS gives the expected result too.

But the query

kolusu · Posted: Mon Feb 10, 2003 6:59 pm Post subject:

vallish,

can you elaborate as to what you meant by "it doesn't seem to be the doing the same"?

Gotluru: your select statement with FETCH FIRST 3 ROWS also works.It is a new feature of db2 v7. Also it wouldn't elimintate duplicates.

Kolusu

vallishar · Posted: Tue Feb 11, 2003 10:26 am Post subject:

Kolusu,

I tried to execute the SQL

kolusu · Posted: Tue Feb 11, 2003 9:44 pm Post subject:

vallishar,

The query works fine for me , can you post your sql so that I can take a look at it?

Thanks

kolusu

vallishar · Posted: Thu Feb 13, 2003 11:19 am Post subject:

Hi Kolusu,

The query gives the correct result for the distinct values of the selected field., in this case emp_sal. When there are duplicate rows, the number of rows selected may not be the same as expected.

I had overlooked the result of my query. Aplogies.

Thanks
_________________
If you're not failing every now and again, it's a sign you're not doing anything very innovative.

Premkumar · Posted: Thu Feb 13, 2003 11:48 pm Post subject:

That is a query to select 'Top n, with ties for the last place' and NOT 'First n'.

Glenn · Beginner Joined: 23 Mar 2003 Posts: 56 Topics: 3

Might I suggest?

rama krishna reddy · Posted: Thu Apr 19, 2007 12:54 pm Post subject: MAX(SAL)

Is there any other way to select the second or third max value in a column..apart from the below query.
select max(sal) from table where sal < (select max(sal) from table)

Hi Madhuri

We can select first max or third max whar ever u want by using below correlated query

select a.emplno ,a.salary from employee a where 0=(select count(*) from employee b where a.salary < b.salary)

In the above case it will give maximum salary ,if u want second max u need to give 1 inspite of 0 ,if u want third max u need to give 2 inspite of 0

eg:

i/p Employee table date is

500
400
200
300
100

query will sort the input table like below

In this case

a b

100 100
200 200
300 300
400 400
500 500

if u consider salary one in this case count will beocome 4 science

100 is less than 200,300,400,500

in case of 500 COUNT will be zero

I hope it will clear u r doubt

Regards
Rama krishna reddy

kolusu · Posted: Thu Apr 19, 2007 3:31 pm Post subject:

rama krishna reddy,

Wrong thread. I guess you intended to post in this topic

http://www.mvsforums.com/helpboards/viewtopic.php?t=8224

Post the solution over there and I will delete it in this topic

Thanks

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu