Labels

January 7, 2014

How to Retrieve/Find Nth highest Value from a table?


I have a table in my mysql database and I want to find out the highest value, 2nd highest value, 3rd highest value and so on. Then how should I deal with this scenario?

Distance Table :~

mysql> desc Distance;
+-----------------+---------------+------+
| Field           | Type          | Null |
+-----------------+---------------+------+
| DistanceId      | bigint(20)    | NO   |
| ZipCode1        | varchar(45)   | NO   |
| ZipCode2        | varchar(45)   | NO   |
| DistanceInMiles | decimal(10,2) | NO   |
+-----------------+---------------+------+

9 rows in set (0.11 sec)

Records in Distance Table :~

mysql> select * from Distance order by DistanceInMiles ;
+------------+----------+----------+-----------------+
| DistanceId | ZipCode1 | ZipCode2 | DistanceInMiles |
+------------+----------+----------+-----------------+
|          1 | 95101    | 95101    |            0.00 |
|          2 | 95101    | 95108    |            0.00 |
|          3 | 44444    | 44444    |            0.00 |
|          8 | 44444    | 44451    |            8.66 |
|          5 | 44444    | 44446    |           11.27 |
|          6 | 44444    | 44449    |           13.85 |
|          7 | 44444    | 44450    |           19.59 |
|         13 | 44444    | 44460    |           20.84 |
|          9 | 44444    | 44452    |           23.17 |
|         10 | 44444    | 44453    |           24.52 |
|         11 | 44444    | 44454    |           29.88 |
|          4 | 44444    | 44445    |           30.08 |
|         12 | 44444    | 44455    |           33.48 |
+------------+----------+----------+-----------------+
13 rows in set (0.00 sec)

Find the Highest Record from the Table

mysql> select * from Distance order by DistanceInMiles desc limit 1;
+------------+----------+----------+-----------------+
| DistanceId | ZipCode1 | ZipCode2 | DistanceInMiles |
+------------+----------+----------+-----------------+
|         12 | 44444    | 44455    |           33.48 |
+------------+----------+----------+-----------------+
1 row in set (0.02 sec)


mysql> select Max(DistanceInMiles) from Distance;
+----------------------+
| Max(DistanceInMiles) |
+----------------------+
|                33.48 |
+----------------------+
1 row in set (0.00 sec)


Find the 2nd Highest Record from the Table

mysql> select Max(DistanceInMiles) from Distance where DistanceInMiles < (select Max(DistanceInMiles) from Distance);

+----------------------+

| Max(DistanceInMiles) |
+----------------------+
|                30.08 |
+----------------------+
1 row in set (0.00 sec)

Now, Find the Nth Highest Record from the Table.

mysql> SELECT DistanceInMiles FROM ( 
         SELECT DISTINCT DistanceInMiles 
           FROM Distance
             ORDER BY DistanceInMiles DESC limit N) a 
                ORDER BY DistanceInMiles limit 1;

where N = Nth Highest record you want to find from the table.


Cases :~ 

    N = 0 - Return an Empty Set
    N >= Total Number of records in Table - Will return MIN value from that table.

For example, if i want to find out the 5th Highest record from the Distance Table then, my Query will be,


mysql> SELECT DistanceInMiles FROM ( 
         SELECT DISTINCT DistanceInMiles 
           FROM Distance 
             ORDER BY DistanceInMiles DESC limit 5) a 
                ORDER BY DistanceInMiles limit 1;
+-----------------+
| DistanceInMiles |
+-----------------+
|           23.17 |
+-----------------+
1 row in set (0.00 sec)