MVSFORUMS.com

[mainframemouli]

I searched the site but i got a Query like the one i have given below to get the Top specified values but if the table contains the duplicates


consider the record is like this

RDS_SKU_NBR
1000
2000
2000
3000
4000

I will order it in desecding order and if give this query then third highest
should be 2000 but its not working but if i give "where 4 ="

then its giving the result 2000.



SELECT A.RDS_SKU_NBR FROM DEV.WMS_LOC_INV A WHERE 3 = (SELECT COUNT(*) FROM

DEV.WMS_LOC_INV B WHERE B.RDS_SKU_NBR >= A.RDS_SKU_NBR)



and also tell me the query for finding the top n highest sku number ??????

[kolusu]

mainframemouli,

Check this link which answers your queries

http://www.mvsforums.com/helpboards/viewtopic.php?t=23&highlight=salary

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

[mainframemouli]

I checked the link the queries given there are like this


This query is for nth highest
SELECT * FROM EMP_TABLE
WHERE SAL >= (SELECT MAX(SAL) FROM EMP_TABLE
WHERE SAL < (SELECT MAX(SAL) FROM EMP_TABLE
WHERE SAL < (SELECT MAX(SAL)
FROM EMP_TABLE)))
ORDER BY SAL DESC
;


This query is for n highest
SELECT *
FROM
EMP_TABLE X
WHERE n > ( SELECT COUNT(*) FROM EMP_TABLE
WHERE SAL > X.SAL )
ORDER BY X.SAL DESC

my questions are

1.if i want the 10th maximum value , shall i have to give 10 subqueries?????

2.If i want the 16th max what i have to do?????

3. Is the Query for N highest value will work if duplicates are there????

[kolusu]

mainframemouli,