Only display first instance of event for each unique person

Question

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

Answer 1

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: