Search code examples
mysqlsqlgroup-bygroupwise-maximum

Get DateTime corresponding to the lowest Time

I'd like to get the Date & ID which corresponds to the lowest and Largest Time, respectively the extreme rows in the table below with ID 5 & 4.

Please note the following:

  • Dates are stored as values in ms
  • The ID reflects the Order By Date ASC
  • Below I have split the Time to make it clear
  • * indicates the two rows to return.
  • Values should be returns as columns, i.e: SELECT minID, minDate, maxID, maxDate FROM myTable
| ID | Date                | TimeOnly  |
|----|---------------------|-----------|
| 5  | 14/11/2019 10:01:29 | 10:01:29* |
| 10 | 15/11/2019 10:01:29 | 10:01:29  |
| 6  | 14/11/2019 10:03:41 | 10:03:41  |
| 7  | 14/11/2019 10:07:09 | 10:07:09  |
| 11 | 15/11/2019 12:01:43 | 12:01:43  |
| 8  | 14/11/2019 14:37:16 | 14:37:16  |
| 1  | 12/11/2019 15:04:50 | 15:04:50  |
| 9  | 14/11/2019 15:04:50 | 15:04:50  |
| 2  | 13/11/2019 18:10:41 | 18:10:41  |
| 3  | 13/11/2019 18:10:56 | 18:10:56  |
| 4  | 13/11/2019 18:11:03 | 18:11:03* |
Solution

  • In earlier versions of MySQL, you can use couple of inline queries. This is a straight-forward option that could be quite efficient here:

    select 
    (select ID from mytable order by TimeOnlylimit 1) minID,
    (select Date from mytable order by TimeOnly limit 1) minDate,
    (select ID from mytable order by TimeOnly desc limit 1) maxID,
    (select Date from mytable order by TimeOnly desc limit 1) maxDate