Monday, February 23, 2009

How to get second maximum value using sql query?

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 intension is to find a record whose c1 value is second highest value.
Query:-
Inner query gives the maximum value available which is 983, so now i am saying to find max value again but whose value should be less than 983 (value from inner query).
Obviously, it is next maximum value.
SELECT MAX(c1) FROM TEST where c1<(SELECT MAX(c1) FROM TEST)
946
So i am getting second highest value from the column of the table.


1 comment:

Anonymous said...

Great tutorial!!. by the way here are couple of more ways to find second highest salary in SQL