0

I am trying to query min value of column and its timestamp.

Table "cars":

+----+------------+-------+
| id | epoch_time | cars  |
+----+------------+-------+
|  1 |          1 |     5 |
|  2 |          2 |    10 |
|  3 |          3 |    15 |
|  4 |          4 |    10 |
|  5 |          5 |     3 |
+----+------------+-------+

Expected result:

+----+------------+-------+
| id | epoch_time | cars  |
+----+------------+-------+
|  5 |          5 |     3 |
+----+------------+-------+

Tried so far: 

SELECT min(cars), epoch_time FROM cars;

It picks up min value fine, but epoch_time is incorrect.

I am expecting epoch_time to be corresponding to min(value) associated (by row).

J. Doe
  • 301
  • 2
  • 13

1 Answers1

2

One option is to use a subquery which finds the minimum value:

SELECT id, epoch_time, cars
FROM cars
WHERE cars = (SELECT MIN(cars) FROM cars);

Or, if you are certain that there would only ever be a single record with the minimum cars value, then we can try using a LIMIT trick:

SELECT id, epoch_time, cars
FROM cars
ORDER BY cars
LIMIT 1;

If you are using MySQL 8+, then we can try using ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY cars) rn
    FROM cars
)

SELECT id, epoch_time, cars
FROM cte
WHERE rn = 1;

Edit:

Any of the three above queries could made fast by adding an appropriate index. An index which would generally work would be:

CREATE INDEX idx ON cars (cars, id, epoch_time);
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360