How to get the first result of rows with duplicate dates?
Hi,
I have a table that contains the following fields:
- An ID
- An event
- A date-time for each event
It looks like this:
ID
| Event
| Event Date
|
1122334
| Event 1
| 2016-10-01 10:00:00
|
1122334
| Event 2
| 2016-10-01 10:00:00
|
1122335
| Event 1
| 2018-06-23 11:43:22
|
1122336
| Event 2
| 2019-02-02 08:04:13
|
1122336 | Event 1 | 2019-02-03 06:07:17
|
1122336 | Event 2 | 2019-02-06 13:37:44
|
My goal is to find the first event for a given ID, based on the date so that I have a list of unique IDs and their corresponding events and dates like so:
ID
| Event
| Event Date
|
1122334
| Event 1
| 2016-10-01 10:00:00
|
1122335
| Event 2
| 2018-06-23 11:43:22
|
1122336
| Event 2
| 2019-02-02 08:04:13
|
The following query gets me close, but due to the fact that there are duplicate values in the date-time column (to the second), I still get duplicate entries:
- SELECT
- "Table_1"."Event"
- FROM (SELECT
- "Table_1"."ID" as "ID",
- MIN("Table_1"."Event Date") AS "Event Date"
- FROM "Table_1"
- GROUP BY "ID"
- ) AS "First Action"
- LEFT JOIN "Table_1" ON "Table_1"."ID" = "First Action"."ID"
- AND "Table_1"."Event Date" = "First Action"."Event Date"
This results in:
ID
| Event
| Event Date
|
1122334
| Event 1
| 2016-10-01 10:00:00
|
1122334
| Event 2
| 2016-10-01 10:00:00
|
1122335
| Event 1
| 2018-06-23 11:43:22
|
1122336
| Event 2
| 2019-02-02 08:04:13
|
For record 1122334 there are still two entries because the date is duplicated. My logical solution would be to select only the first row, but I am not sure how to do this.
I can't change the data in the underlying table, and I tried various implementations of DISTINCT and COUNT(*) that didn't yield good results.
I would appreciate any assistance in getting the correct SQL query to get the results.