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 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
pgpQ7novDk8tC.pgp
Description: PGP signature
