Get DateTime corresponding to the lowest Time

Question

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* |

See: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Strawberry, Nov 15 '19 at 10:21

Tim Biegeleisen · Answer 1 · 2019-11-15T10:34:03.077

2

One option for MySQL 8+, using ROW_NUMBER with pivoting logic:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY TimeOnly) rn_min,
        ROW_NUMBER() OVER (ORDER BY Date TimeOnly) rn_max
    FROM yourTable
)

SELECT
    MAX(CASE WHEN rn_min = 1 THEN ID END) AS minID,
    MAX(CASE WHEN rn_min = 1 THEN Date END) AS minDate
    MAX(CASE WHEN rn_max = 1 THEN ID END) AS maxID,
    MAX(CASE WHEN rn_max = 1 THEN Date END) AS maxDate
FROM cte;

Here is an option for MySQL 5.7 or earlier:

SELECT
    MAX(CASE WHEN pos = 1 THEN ID END) AS minID,
    MAX(CASE WHEN pos = 1 THEN Date END) AS minDate
    MAX(CASE WHEN pos = 2 THEN ID END) AS maxID,
    MAX(CASE WHEN pos = 2 THEN Date END) AS maxDate
FROM
(
    SELECT ID, Date, 1 AS pos FROM yourTable
    WHERE TimeOnly = (SELECT MIN(TimeOnly) FROM yourTable)
    UNION ALL
    SELECT ID, Date, 2 FROM yourTable
    WHERE TimeOnly = (SELECT MAX(TimeOnly) FROM yourTable)
) t;

This second 5.7 option uses similar pivoting logic, but instead of ROW_NUMBER is uses subqueries to identify the min and max records. These records are brought together using a union, along with an identifier to keep track of which record be min/max.

edited Nov 15 '19 at 10:34

answered Nov 15 '19 at 10:20

Tim Biegeleisen

502,043
27
286
360

Thanks for the fast answer. Sadly I'm not at version 8, but still 7.x : – Enissay Nov 15 '19 at 10:24
@Enissay I updated with an option for earlier versions of MySQL. – Tim Biegeleisen Nov 15 '19 at 10:30
Shouldn't it be `WHERE TimeOnly = (SELECT MIN(TimeOnly) FROM yourTable)` ? – Pepper Nov 15 '19 at 10:31
*"Thanks for the fast answer. Sadly I'm not at version 8, but still 7.x"* Uhm @Enissay did MySQL 7.x exist ? MySQL went from MySQL 5.7 directly to MySQL 8 i guess they found MySQL 8 to be so fine they skipped a few major version numbers. – Raymond Nijland Nov 15 '19 at 10:51
@RaymondNijland Haha, I noticed my mistake later but ignored it as it can be deduced anyway... I meant indeed 5.7 ;-) – Enissay Nov 15 '19 at 12:25
Thank you, smart way to do it. More conditions needs to be added to the sub WHERE clause to avoid retrieving the wrong value occurring at the same time. – Enissay Nov 15 '19 at 12:39

score 2 · Accepted Answer · answered Nov 15 '19 at 10:32

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

Salman A · Answer 3 · 2019-11-15T10:51:26.963

2

You could simply do this:

SELECT minval.ID, minval.Date, maxval.ID, maxval.Date
FROM (
    SELECT ID, Date
    FROM t
    ORDER BY CAST(Date AS TIME)
    LIMIT 1
) AS minval
CROSS JOIN (
    SELECT ID, Date
    FROM t
    ORDER BY CAST(Date AS TIME) DESC
    LIMIT 1
) AS maxval

If you want two rows then change CROSS JOIN query to a UNION ALL query.

Demo on db<>fiddle

edited Nov 15 '19 at 10:51

answered Nov 15 '19 at 10:46

Salman A

262,204
82
430
521

Get DateTime corresponding to the lowest Time

3 Answers3