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
+------------+----------+----------+-----------------+
| 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)
No comments:
Post a Comment