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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
