MySQL ODBC Connector - Query: Order for the Earliest Latest date

Author

Query: Order for the Earliest Latest date

Dan Baker

2006-01-24, 1:23 pm

[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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Greg Fortune

2006-01-24, 8:23 pm

--nextPart3197006.xquNBLJGMb
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

In addition to the id_Site, you also need to grab the MAX(Time) so you have=
=20
something to sort by. This requires a little trick known as a "groupwise=20
maximum." See=20
http://dev.mysql.com/doc/mysql/en/e...-group-row.html for=
=20
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 >=3D $date1 AND Time <=3D $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

--nextPart3197006.xquNBLJGMb
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQBD1oD8BGtxgQ8X
NB4RAjdBAKCVxvkYF3oK
zFh9lY3hwCHsF/2GLgCfX8NM
Ja8TVlHGwZ/NyvMVvz2G/gA=
=Pmts
-----END PGP SIGNATURE-----

--nextPart3197006.xquNBLJGMb--

Peter Brawley

2006-01-24, 8:23 pm

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/mysql? unsub...sie.nctu.edu.tw

Dan Baker

2006-01-24, 8:24 pm

"Peter Brawley" <peter.brawley@earthlink.net> wrote in message
news:43D6AB3A.9090105@earthlink.net...
> Dan,
>
>
> 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/mysql? unsub...sie.nctu.edu.tw

Sponsored Links