Get a field's value associated with a function's selected row/field

I have the next events table

event_id	course_id	start	end	creator_id
1	1	8	1	12
2	1	1	3	13
3	1	4	5	14
4	2	5	7	15
5	2	9	9	16
6	2	2	2	17
7	3	7	4	18
8	3	3	6	19
9	3	6	8	20

And when i run this query:

SELECT course_id,              COUNT(*) AS event_count,              MIN(start) as earliest,              MAX(end) as latest          FROM event          GROUP BY course_id  

I get the next result:

course_id	event_count	earliest	latest
1	3	1	5
2	3	2	9
3	3	3	8

So, it groups by course_id and for each course_id it gives me the min(start) as earliest and max(end) as latest value between events that share the same course_id.

Now what i need is: to also get the creator_id associated with those min(start) and max(end) rows, so for example: If for:

• course_id = 1, earliest is 1, i also need the creator_id associated with that row, which would be 13, and so on,
• for course_id = 2, earliest is 2, i also need the creator_id associated with that row, which would be 17
• for course_id = 3, earliest is 3, i also need the creator_id associated with that row, which would be 19

So the query result i'm looking for is:

course_id	event_count	earliest	latest	earliest_creator_id
1	3	1	5	13
2	3	2	9	17
3	3	3	8	19

Is there a way to add that to the query as a column with an alias in any way? I hope that the explanation is understandable, thanks in advance!

https://stackoverflow.com/questions/65783778/get-a-fields-value-associated-with-a-functions-selected-row-field January 19, 2021 at 08:05AM