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
