-1

I have a table below (that also has several other columns, but for the purpose of this example, I'll exclude them) where I only want to include the very first instance for each person (unique_id) by date, which is in DATETIME format.

In the past I've used something like:

SELECT *, least(min(date_event), min(date_event)) as min_date FROM table GROUP BY unique_id ,issue, date_event, age_at_event

However, this is still returning multiple records for each person, rather than just the very first instance?

unique_id issue date_event age_at_event
1234 issue_a 2016-04-01T00:00:00 6
1234 issue_a 2016-04-01T00:00:00 6
1234 issue_b 2018-04-01T00:00:00 8
5678 issue_a 2019-09-01T00:00:00 2
5678 issue_a 2021-09-01T00:00:00 4
65431 issue_c 2019-09-01T00:00:00 1
1234 issue_a 2022-09-01T00:00:00 12
Raven52
  • 77
  • 11

1 Answers1

2

You can use the qualify function to implement what you're looking for. With the sample data you provided the following query:

select  *
from sample_data
qualify row_number() over (partition by unique_id order by date_event) = 1

produces this: enter image description here

Daniel Zagales
  • 2,948
  • 2
  • 5
  • 18