Monday, February 23, 2009

How to get Nth maximum value using sql query?

Please refer
To find 2nd maximum value using sql query in Prog. Finidngs .

I would like to take the same table considered in above link to find Nth maximum value.

Let us consider a database table named “Test” and which has a column named “C1”.
Let the data type of C1 be Number
Script:-

CREATE TABLE test
(
c1 NUMBER
)
I have inserted some random data using
INSERT INTO TEST
SELECT ROUND(dbms_random.value(1,1000),0) FROM dual
Multiple times to insert multiple records.
I have inserted 66 records into this test table



Data (first 30 records)



So we can observe that there are many number of records among which the record with maximum value of C1 with my test data is
select max(c1) from test
983




Now my intention is to find a record whose c1 value should be the highest nth value.
Query:-
I would like to get the 10th highest value of C1 from test table highlighted in below screen shot.
SELECT * FROM TEST ORDER BY c1 DESC




So,
SELECT c1 FROM
(SELECT ROWNUM AS MAXINDEX,c1 FROM
(SELECT * FROM TEST ORDER BY c1 DESC)) WHERE MAXINDEX=10
helps me to get the Nth maximum value.
Innermost query gives me set of records from Test table in descending order of C1 column, the next inner query helps to identify the ordered records based on “Rownum”.
Using this rownum, external query can identify specific record at Nth position.
My test data which has 10th maximum value is
853
So i am getting 10th highest value from the column of the table.


5 comments:

Anonymous said...

I would agree this would work if the column has distinct values. What if the column has more than one instance of same value.

for instance a column with
1,2,5,5,5,7,7,9,2,2 Then the rownum usage would fail.

Anonymous said...

If it has more than one instance of the same value - use a group by

SELECT c1 FROM
(SELECT ROWNUM AS MAXINDEX,c1 FROM
(SELECT c1 FROM TEST GROUP BY c1 ORDER BY c1 DESC)) WHERE MAXINDEX=10

Anonymous said...

yeah, you can either use group by or distinct

SELECT c1 FROM
(SELECT ROWNUM AS MAXINDEX,c1 FROM
(SELECT distinct c1 FROM TEST ORDER BY c1 DESC)) WHERE MAXINDEX=N

or
SELECT c1 FROM
(SELECT ROWNUM AS MAXINDEX,c1 FROM
(SELECT * FROM TEST group BY c1 DESC ORDER BY c1 DESC)) WHERE MAXINDEX=N

Anonymous said...

Thanks for the article. You saved me a ton of time !

SuBH said...

Thanks, for the article.........