"Peter Brawley" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Dan, > >>I'm trying to generate a list of sites that HAD a support incident within >>a known date range, and order them so that the site that has the OLDEST >>support call is FIRST in the list. > > It's the (oft-asked-for) groupwise-max query. Here's one way, assuming you > have MySQL 4.1 or later ... > > SELECT > id_site, > time AS 'Earliest Last Support' > FROM incident AS i1 > WHERE time = ( > SELECT MAX( e2.time) > FROM incident AS i2 > WHERE i2.id_site = i1.id_site > ) > ORDER BY id_site; > > If your MySQL version is earlier than 4.1, change the subquery to a stage > 1 query into a temp table then select & order by from that.
Bummer ... I'm running MySQL 4.0. I've never done a temp-table query. But, I'll give it a shot! Thanks DanB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
