Hi Bruce,
bruce wrote:
hi...
a further test...
the following test tbl/information:
dog
name char
status int
_date timestamp
id int
<snip>
here's the tricky part. if i want to get the row with the status=3, but only
if there's not a status=4 that has a later date, how do i accomplish
this...??
so, for tom, i would return 'null', and for sue, i'd return '3' for the
'01/24/07' the date for the last '3' is later than the date for the last
'4'...
i imagine that there's a way to accomplish this using subselects.
any thoughts/comments..
There are three ways to solve this. One involving a subquery, one
involving a self-join, and another involving a temporary table. I will
demonstrate all three.
temp table solution (attempt to match the condition you are testing
against and only keep those rows that do not match):
CREATE TEMPORARY TABLE tmpStatus3
SELECT id, name, status, _date
FROM dog
WHERE status=3;
SELECT s3.id, s3.name, s3.status, s3._date
FROM tmpStatus3 s3
LEFT JOIN dog
ON dog.name = s3.name
AND dog.status = 4
AND dog._date > s3.date
WHERE dog.id is null;
self-join solution:
SELECT d1.id, d1.name, d1.status, d1._date
FROM dog d1
LEFT JOIN dog d2
ON d1.name = d2.name
AND d2.status = 4
AND d2._date > d1._date
WHERE d1.status = 3
AND d2.id is null;
subquery solution (1):
SELECT d1.id, d1.name, d1.status, d1._date
FROM dog d1
WHERE d1.status = 3
AND NOT EXISTS (SELECT id FROM dog d2 WHERE d1.name = d2.name
AND d2.status = 4
AND d2._date > d1._date);
subquery solution (2):
SELECT d1.id, d1.name, d1.status, d1._date
FROM dog d1
LEFT JOIN (SELECT name, _date FROM dog WHERE status = 4) d2
ON d2.name = d1.name
AND d2._date > d1._date
WHERE d1.status = 3
AND d2.name is NULL ;
Typically, the techniques used in "temporary table" or "subquery
solution (2)" will provide the best results as you only need compute the
comparison set of data once. The "self-join" technique creates a
Cartesian product which degrades in performance with the square of the
number of rows being compared and will be slower than the dependent
subquery evaluation of the EXISTS() clause used in "subquery solution
(1)" .
Does this demonstration help you see some additional patterns you can
use to solve this and perhaps a few other similar query problems?
--
Shawn Green, Support Engineer
MySQL Inc., USA, www.mysql.com
Office: Blountville, TN
Are you MySQL certified? www.mysql.com/certification
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
