HOW TO GET THIRD HIGHEST VALUE 3

[solanki123]

I want to retrieve records from table based on third highest value of a column.
Please help me...

 

[MarcLodge]

If you have DB2 &.1 available and the ability to use OLAP functions, you might be able to use RANK, although I have never done this. See here:

http://www-3.ibm.com/cgi-bin/db2www...d2w/report?fn=db2v7s0olapfunc.htm#HDROLAPFUNC

for further info.

That said, I would probably prefer to write a cursor with an order by clause, and programatically obtain the 3rd value.

Marc

 

[solanki123]

Helo Mark, Thank you. Your guidance is helpful.

 

[gregsimpson]

Solanki123,

if your up to Version7 of DB2 you could order by the column your interested in and use the FETCH FIRST 3 ROWS predicate.

Thanks
Greg

 

[sathyarams]

Check

http://dbforums.com/t843861.html

HTH

Sathyaram

More DB2 questions answered at

http://www.dbforums.com/f8

 

[MarcLodge]

Using the FETCH FIRST 3 ROWS predicate will optimise the query for performance, but you will still have to issue 3 Fetch's in order to obtain the data. Sathyaram's method in dbforums has potential, but is only 2nd row, not the third. I'm not sure if the method could be effectively used for the third row.

It seesm the best way would be to open a cursor with the FETCH FIRST 3 ROWS predicate, and then programatically issue 3 fetch's. Unless somebody knows different......
hth
Marc

 

[sathyarams]

The third post in the thread in dbforums I mentioned above:

select * from employee e1 where (select count(distinct salary) from
employee e2 where e2.salary>=e1.salary)=n

returns the n-th highest value ... If n is 2, then second highest value, n is 3 then 3rd highest value etc ..

Cheers

Sathyaram

More DB2 questions answered at

http://www.dbforums.com/f8

 

[MarcLodge]

Sorry Sathyaram, I didn't page down that far! Having looked at the SQL though, I'm a bit dubious as to what would happen if there existed rows with the same salary, particularly as there doesn't seem to be an order by of any sorts. Also, I've just cut and paste it into a query, and it gives me a syntax error. Curious.

 

[MarcLodge]

Hi All,

I changed the SQL slightly to:

SELECT *
FROM EMPLOYEE E1
WHERE 3 =
(SELECT COUNT(DISTINCT E2.SALARY) FROM
EMPLOYEE E2 WHERE E2.SALARY<=E1.SALARY)

which worked and gave the record with the third highest salary. Very clever, Sathyarams. Give whoever posted that in dbforums a star!

Marc

 

[solanki123]

Neither Sathyaram's query nor MarcLodge's query is working.
Without testing the query I gave * to MarcLodge.
I have a table name emp which has 2 columns ename and esal.
And the table has following records.

ename esal

solanki 18,000.50
manjith 15,000.50
azhil 15,000.50
bala 14,000.50
vinodh 12,000.75

When I tried Sathyaram's query with n=3 it is giving error.When I tried MarcLodge's query it is retrieving 2 records as follows
manjith 15,000.50
azhil 15,000.50

But I want third hieghest which is
bala 14,000.50

 

[MarcLodge]

Hi Solanki,
Can't be given a star in error!

Try swapping the <= to a >= which should give you what you need, as in:
SELECT *
FROM EMPLOYEE E1
WHERE 3 =
(SELECT COUNT(DISTINCT E2.SALARY) FROM
EMPLOYEE E2 WHERE E2.SALARY>=E1.SALARY)

 

[sathyarams]

Just curious, using the above query I got right value ... Please find below .. Can you let me know what error you got ..

Thanks

sathyaram

$ db2 -tvf salqry
select * from sal

ENAME ESAL
---------- -----------
solanki 18000
manjith 15000
azhil 15000
bala 14000
vinoth 12000

5 record(s) selected.


select * from sal e1 where (select count(distinct esal) from sal e2 where e2.esal>=e1.esal)=3

ENAME ESAL
---------- -----------
bala 14000

1 record(s) selected.



More DB2 questions answered at

http://www.dbforums.com/f8

 

[MarcLodge]

Sathyarams,
I think he will get the same result that you do, as I'm sure it is correct. If you look at my post on the 14th I had <= as I had been playing around with it. If he had run it that way round, he would have got the third lowest value, ie. both the 15,000.50 records.

Does prove the point though that you ought to cater for more than one row returned.

Marc

 

[solanki123]

Hi Mark now your modified query is working fine.
Many many thanks to you.Now my old * is valid.
Sathyaram,
Your query is giving sqlcode=-104: illegal symbol=&quot;count&quot;.

 

[MarcLodge]

Thanks for the feedback and star Solanki, glad to been of help.