MVSFORUMS.com

rahul780 · Beginner Joined: 04 Jan 2008 Posts: 10 Topics: 9

hi all,

i have a SQL query which find 3rd maximum salary from table but i can't understand how this work takes place

Query
SELECT SALALARY FROM EMPINFO A
WHERE 3 = (SELECT COUNT (DISTINCT (SALARY) FROM EMPINFO
WHERE SALARY > = A.SALARY
THIS IS CORELATED QUERY

AND IN MY TABALE SALARY COLLOME CONSIT FOLLOWING SALARYS

SALARY
10000
10000
40000
30000
50000
60000
60000

This query give proper output that is 3rd maximum salary=40000
but how this query finds this

regards
rahul

vkphani · Intermediate Joined: 05 Sep 2003 Posts: 483 Topics: 48

rahul,

That 3 is nothing but row id. Each value of the column salary is comapred against rest of the values in the same column.

First 10000 is compared with all the other values and the count will be 5 as there are 5 values greater than or equal to 10000 in that column.

Then 30000 is compared and the count will be 4.

Then 40000 is compared and the count will be 3.

Then 50000 is compared and the count will be 2.

Then 60000 is compared and the count will be 1.

So based on the row id 3, 40000 is given as result.

NASCAR9 · Posted: Thu Jan 31, 2008 1:32 pm Post subject:

So I thought I might be able to use a query like this in the future. I've spent about 1 hours trying to make a simple query work with zero luck. Maybe someone can point out my error(s). I made a couple of small changes to my query like '(' , name the table. Also I tried COUNT(*).

kolusu · Posted: Thu Jan 31, 2008 1:47 pm Post subject:

NASCAR9,

try this

NASCAR9 · Posted: Thu Jan 31, 2008 2:14 pm Post subject:

Thank you kolusu! Your query worked fine.
_________________
Thanks,
NASCAR9

rahul780 · Beginner Joined: 04 Jan 2008 Posts: 10 Topics: 9

thank you vkphani and kolusu for giving help
the query given by kolusu wokes

thanks
rahul