List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:January 24 2006 10:33pm
Subject:Re: Query: Order for the Earliest Latest date
View as plain text  
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  0    when 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

Thread
Query: Order for the Earliest Latest dateDan Baker24 Jan
  • Re: Query: Order for the Earliest Latest dateGreg Fortune24 Jan
  • Re: Query: Order for the Earliest Latest datePeter Brawley24 Jan
  • Re: Query: Order for the Earliest Latest dateDan Baker25 Jan