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.

Advertisements


3 Responses to “Find Second Or nth Highest Value In A SQL Database Table Column”

  1. Indeed great tutorial, by the You can also use this way to find second highest salary in MySQL and SQ Server 2008

  2. 3 ranjanm

    really good one


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: