CODEWITHSUNDEEP
sqlteradataminmax
IcySkullz
IcySkullz

Reputation: 33

Min/Max timestamp follow by specific date

Table consists of the following:

+------+----------------+--------------------------------------+
| ID   | Date           | Timestamp                            |
+------+----------------+--------------------------------------+
| 1    | 01-01-2020     | 01-01-2020 10:00:00                  |
| 1    | 06-01-2020     | 06-01-2020 23:00:00                  |
| 1    | 03-02-2020     | 03-02-2020 10:00:00                  |
| 2    | 06-01-2020     | 06-01-2020 12:49:00                  |
| 2    | 07-03-2020     | 07-03-2020 10:51:00                  |
| 3    | 23-01-1992     | 23-01-1992 09:00:00                  |
| 3    | 23-01-1992     | 23-01-1992 10:00:00                  |
+------+----------------+--------------------------------------+

I would like to get the ID with minimum timestamp date of '06-01-2020', the answer should be ID #2

SELECT

ID,
MIN(Timestamp)

FROM Table
WHERE Date = '06-01-2020'
GROUP BY ID

My understanding of the above will filter the date first before getting the minimum timestamp which gives an incorrect result of ID #1 and #2. Please advise.

Upvotes: 0

Views: 2637

Answers (3)

dnoeth
dnoeth

Reputation: 60462

This returns IDs with a minimum timestamp on 06-01-2020

SELECT
    ID,
    MIN(Timestamp)
FROM Table
GROUP BY ID
HAVING min(Date) = date '2020-01-06'

or without the date column

HAVING cast(min(timestamp as date)) = date '2020-01-06'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You seem to want two conditions:

  • MIN(timestamp) per id
  • ID that have records on a particular date

You can HAVING:

SELECT ID, MIN(Timestamp)
FROM Table
GROUP BY ID
HAVING SUM(CASE WHEN Date = '2020-06-01' THEN 1 ELSE 0 END) > 0;

This aggregates by id and returns the minimum timestamp. It then filters the ids so only the ones that have a date of 2020-06-01.

If you '2020-06-01' is the most recent date in the table you could simplify that to:

HAVING MAX(Date) = '2020-06-01'

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

Try first finding the minimum values across the entire table, then restrict to the date of interest:

SELECT ID, min_timestamp
FROM
(
    SELECT ID, MIN(Timestamp) AS min_timestamp
    FROM yourTable
    GROUP BY ID
) t
WHERE Date = '2020-06-01';

Upvotes: 1

Related Questions