To select the nth highest record from a database, one need to do following steps:
- First, get the n highest records and sort them in ascending order. The nth highest record will be the last record in the result set.
- Then sort the result set in descending order and get the first one.
Now let us write a query for step 1:
SELECT * FROM name_of_table ORDER BY name_of_column ASC LIMIT N;
With above query our result set have records in ascending order and our nth element is present at last of result set.
Now let us write a query for step 2:
SELECT * FROM (SELECT * FROM name_of_table ORDER BY name_of_column ASC LIMIT N) AS asc_tbl ORDER BY name_of_column DESC LIMIT 1;
With above query we have inversed our step 1 result set so our nth element is present at start of result set and using LIMIT 1 in our query we have limited our result set to size 1 i.e. our element.
It looks easy isn’t it, but we will see easier approach than above approach.
We can use LIMIT clause provided by MySQL to limit the number of records required in a result set.
SELECT * FROM name_of_table ORDER BY name_of_column DESC LIMIT n - 1, 1;
The query will return the first row after n-1 row(s) so we get the nth highest record in result set.