• Guest, The rules for the P & N subforum have been updated to prohibit "ad hominem" or personal attacks against other posters. See the full details in the post "Politics and News Rules & Guidelines."

Need some sql help / group by/ aggergate function

rsutoratosu

Platinum Member
Feb 18, 2011
2,710
4
81
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

Senior member
Aug 16, 2012
649
90
91
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

Platinum Member
Feb 18, 2011
2,710
4
81
TY, theres no way without joins right ? or is there other methods... not very good with sql
 

Fallen Kell

Diamond Member
Oct 9, 1999
5,535
192
106
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.
 
Last edited:

ASK THE COMMUNITY