Need some sql help / group by/ aggergate function

[rsutoratosu]

I dont know sql that well , so i'm not sure whats the best way. For each of the ID, i'm look for event start and completed time,
ie i'm doing select min(datetime), max(datetime) from Table group by ID which works but if i'm trying to find the function associated wth the min(datetime) - should be Event start. and max(datetme) - event completed, I'm getting all sort of extra data or too much data.

The result im looking for is , let me know any suggestions or things o try.. thanks !

ID 1, start time, function, end time, function
ID 2, start time, function, end time, function


I have a table that has id, date time, function ie
ID Datetime Function
1 2021-02-18 1:00pm Event start
1 2021-02-18 1:01pm Do XYZ
1 2021-02-18 1:02pm Do 123
1 2021-02-18 1:03pm Do xxx
1 2021-02-18 1:04pm Do yyy
1 2021-02-18 1:05pm Event completed

2 2021-02-19 1:00pm Event start
2 2021-02-19 1:01pm Do XYZ
2 2021-02-19 1:02pm Do 123
2 2021-02-19 1:03pm Do xxx
2 2021-02-19 1:04pm Do yyy
2 2021-02-19 1:05pm Event completed

3 2021-02-21 5:00pm Event start
3 2021-02-21 5:01pm Do XYZ
3 2021-02-21 5:02pm Do 123
3 2021-02-21 5:03pm Do xxx
3 2021-02-21 5:04pm Do yyy
3 2021-02-21 5:05pm Event completed

 

[kn51]

Self join will do it...

select e1.id, e1.eventdate,e1.function,e2.eventdate,e2.function

from events e1, events e2

where e1.id=e2.id
and e1.function="event start"
and e2.function="event completed"

 

[rsutoratosu]

TY, theres no way without joins right ? or is there other methods... not very good with sql

 

[Fallen Kell]

I would think you can do something like the following:

select id, eventdate, function from tablename where function="Event start" or function="Event completed" group by id order by eventdate

But that would need to be processed again. If you truely need the output like you stated, you would need to create either a temp table with the query and/or join the results from two queries like example that kn51 presented.