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
CREATE TABLE test
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
Now my intention is to find a record whose c1 value should be the highest nth value.
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
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
So i am getting 10th highest value from the column of the table.