How to select the nth highest record in a database table?

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.


FOLLOW US ON LinkedIn



Explore Tutu'rself