Re: Query: Order for the Earliest Latest date
"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]
Re: Query: Order for the Earliest Latest date
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. HTH. PB - Dan Baker wrote: [GENERAL INFO] I have two tables I'm working with. One table (Sites) contains contact information for every customer site that we deal with. The other table (Incidents) contains all the support calls we've made. [QUERY] 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. I'm using: SELECT DISTINCT id_Site FROM Incident WHERE Time >= $date1 AND Time <= $date2 ORDER BY Time DESC Which gives me a list of sites that had a support incident between the dates, but doesn't really sort them correctly. It simply orders them by who had the earliest support call. I'm looking for the site who's LAST support call is the EARLIEST. [Incident TABLE] Field Type Null Default Links to Comments MIME id int(11) No Time int(11) No 0when call came in text/plain Description varchar(100) No brief description Notes text No operator notes id_Site int(11) No 0 site -> id ... Thanks for any pointers. DanB -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date: 1/23/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query: Order for the Earliest Latest date
In addition to the id_Site, you also need to grab the MAX(Time) so you have something to sort by. This requires a little trick known as a "groupwise maximum." See http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html for an explanation and some examples. Greg Fortune On Tuesday 24 January 2006 09:06, Dan Baker wrote: > [GENERAL INFO] > I have two tables I'm working with. One table (Sites) contains contact > information for every customer site that we deal with. The other table > (Incidents) contains all the support calls we've made. > > [QUERY] > 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. > I'm using: > SELECT DISTINCT id_Site FROM Incident > WHERE Time >= $date1 AND Time <= $date2 > ORDER BY Time DESC > Which gives me a list of sites that had a support incident between the > dates, but doesn't really sort them correctly. > It simply orders them by who had the earliest support call. I'm looking > for the site who's LAST support call is the EARLIEST. > > [Incident TABLE] > Field Type Null Default Links to Comments MIME > id int(11) No > Time int(11) No 0when call came in text/plain > Description varchar(100) No brief description > Notes text No operator notes > id_Site int(11) No 0 site -> id > ... > > Thanks for any pointers. > DanB pgpQ7novDk8tC.pgp Description: PGP signature
