SQL min / max on request

Question

I have something like the following data structure:

  Category StartDateTime EndDateTime
 =================================================
 1 12/1/2009 12:00 12/1/2009 12:12
 1 12/1/2009 04:00 12/1/2009 04:20
 2 12/2/2009 10:15 12/2/2009 10:22
 2 12/2/2009 11:00 12/2/2009 11:01

I want min StartDateTime and max EndDateTime for each category. Like this:

  Category MinStartDateTime MaxEndDateTime
 =================================================
 1 12/1/2009 12:00 12/1/2009 04:20
 2 12/2/2009 10:15 12/2/2009 11:01

Using min and max with a group by category does not work:

select Category, min(StartDateTime) [MinStartDateTime], max(EndDateTime) [MaxDateTime] from MyTable group by Category order by Category, StartDateTime, EndDateTime 

I also tried two internal subquery joins for each min and max statement, however it seems to exclude some entries:

 select distinct T1.Category, T1.StartDateTime [MinStartDateTime], T1.EndDateTime [MaxEndDateTime] from MyTable T1 inner join (select Category, min(StartDateTime) [MinStartDateTime] from MyTable group by Category) T2 on T2.Category = T1.Category and T2.MinStartDateTime = T1.StartDateTime inner join (select Category, max(EndDateTime) [MaxEndDateTime] from MyTable group by Category) T3 on T3.Category = T1.Category and T3.MaxEndDateTime = T1.EndDateTime order by T1.Category, T1.encodeStartDateTime, T1.encodeEndDateTime 

Any ideas? A database is a Sybase ASE that must meet the requirements of SQL-92.

Answer 1

Your first solution looks correct except for the order by clause; try:

 select Category, min(StartDateTime) [MinStartDateTime], max(EndDateTime) [MaxDateTime] from MyTable group by Category order by Category, MinStartDateTime, MaxDateTime