Find Second Or nth Highest Value In A SQL Database Table Column

28Jun11

Finding highest or lowest number in a column is easy, you can do that with max() and min() SQL function. But I had recently been asked in an interview to write a query that could find the second highest value in a table column.

Ooops, and I didn’t do well in that interview either.LOL

Before showing you the query, first, let me make things more easy for you. Here is the table script for our very simple JOB table.

CREATE TABLE AAMT.JOB
(
JOB_ID       INTEGER,
DESCRIPTION  VARCHAR2(50 BYTE)
);

Done? Lets move on and insert a few records in JOB table.

Insert into JOB   (JOB_ID, DESCRIPTION) Values   (1, ‘Teaching’);
Insert into JOB   (JOB_ID, DESCRIPTION) Values   (2, ‘Programming’);
Insert into JOB   (JOB_ID, DESCRIPTION) Values   (3, ‘Administration’);
Insert into JOB   (JOB_ID, DESCRIPTION) Values   (10, ‘DBA’);
Insert into JOB   (JOB_ID, DESCRIPTION) Values   (5, ‘Pilot’);
Insert into JOB   (JOB_ID, DESCRIPTION) Values   (8, ‘Security’);
COMMIT;

OK, ladies and gentlemen here goes an Oracle query that does the same. Rownum is our thing here, rownum will serve our purpose .I hope it would be helpful for you.

select job_id from
(select rownum r,job_id from job order by job_id desc)
where r=2;

This is another version I found on web

SELECT job_id FROM
(SELECT job_id,row_number() OVER (ORDER BY JOB_ID DESC )r  FROM job)
WHERE r=2;

Both queries return

8

I hope this would help.

Please leave feedback or if you have better solution, please share.

Filed under: Oracle, SQL, TOAD, Tutorials, Uncategorized   |  Leave a Comment
Tags: database, highest number, Oracle, queries, second highest, SQL