SQL min / max group by question


SQL min / max group by 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 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.




How to change the collation of sqlite3 database to sort case insensitively?

1:



How to “order by” a column and “Include Column Name with query”?
Your first solution looks correct except for the order by clause; try:.
SQL Four Tables One Recordset
select     Category,     min(StartDateTime) [MinStartDateTime],     max(EndDateTime) [MaxDateTime] from MyTable group by     Category order by     Category,     MinStartDateTime,     MaxDateTime 


What's wrong with this SQL statement?


80 out of 100 based on 80 user ratings 830 reviews

@