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)


Advertisements

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