SQL min / max group by question

sap-ase, sql

Solution

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

Problem

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 the 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 & max with a group by Category doesn't seem to work: ``` select Category, min(StartDateTime) [MinStartDateTime], max(EndDateTime) [MaxDateTime] from MyTable group by Category order by Category, StartDateTime, EndDateTime ``` I also tried two inner joins on a sub-query for each min and max statement, however it seems to be excluding some records: ``` 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? The database is Sybase ASE which should be SQL-92 compliant.

Original source