How to retrieve this information?

[vulc]

Dear all,

I have following kind of table:

ID | StartTime | StopTime
------------------------------
1 | 9.00AM | 9.15AM
2 | 10.00AM | 11.00AM
3 | 10.10AM | 10.30AM
3 | 10.40AM | 10.50AM
4 | . | .
5 | . | .
5 | . | .
6 | |

I need to retrieve StartTime and StopTime for each ID. The problem is if there are two rows with the same ID, I need to get the StartTime from the first ID and StopTime from the last ID. Like for ID 3, the StartTime would be 10.0AM and stop Time 10.50AM.

Result needs to be like this:

ID | StartTime | StopTime
------------------------------
1 | 9.00AM | 9.15AM
2 | 10.00AM | 11.00AM
3 | 10.10AM | 10.50AM
4 | . | .
5 | . | .
5 | . | .
6 | |

I've been thinking of creating two different SQL queries, one for getting the ID&StartTime and the second one for getting ID&StopTime. Any ideas how to do this?

One solution could be code it on server side, but I think there might easy solution to fetch the information also with SQL sentences.

 

[fluteplr]

select id, min(starttime) starttime, max(stoptime) stoptime
from mytable
group by id
order by id