How to select Second largest value from a database.

Why this question is important ?

This is a most frequently asked question in an interview these days. This question tests the thinking of a candidate and how much the candidate knows about the database. This question  cannot be done without the use of a sub query and most of the people are not comfortable writing the sub-queries. So if you are not very comfortable with database queries you might get tricked in this question.

Example 

Suppose we have a table with id's from 1 to 1000 and we need to find the second largest id from this table.
Let's say we have table

create table test(id int not null primary_key, name varchar(50));

So we can get the second largest value of id in the following ways.

• SELECT max(id) FROM test WHERE id!=(SELECT MAX(id) FROM test GROUP BY id) GROUP BY id;

• SELECT MIN(id) FROM test (SELECT id FROM test ORDER BY id DESC LIMIT 2) t ;

In Query 1 : SELECT max(id) FROM test WHERE id!=(SELECT MAX(id) FROM test GROUP BY id) GROUP BY id;
We first find the largest id from the table "test" by the sub query "(SELECT MAX(id) FROM test GROUP BY id)", in our case this will give 1000 .Now we select the max "id" from table test where id is not equal to this id, thus it will give 999 as the max id.

In Query 2 : SELECT MIN(id) FROM test (SELECT id FROM test ORDER BY id DESC LIMIT 2) t ;
We first find the number of records in decreasing order that we have to find, suppose we have to find the second largest value then we will find 2 records, if we need to find the third largest value then we need to find the three largest records.SELECT id FROM test ORDER BY id DESC LIMIT 2, gives us 2 records for id = 1000 and 999. Now we select the id with the minimum value, i.e id =999, which is minimum between 1000 and 999.

Using Query 2, we can find out any largest value from  given table. We just need to change the LIMIT N where 'N' is the n-largest value in the table e.g for fourth largest value it will be LIMIT 4 and for 10th largest value it will be LIMIT 10.