David,
I want to select for a given date all sites that have
the last history record with a certain status.
Try something like ...
SELECT h1.supplyorderid
FROM supplyorder o
JOIN supplyorderhistory h1 ON o.uid=h1.supplyorderuid
LEFT JOIN supplyorderhistory h2
ON h2.supplyorderuid = h.supplyorderuid
AND h1.status=2
AND h1.uid < h2.uid
WHERE o.date=<given_date>
AND h2.uid IS NULL
There's a brief discussion of this query pattern at
http://www.artfulsoftware.com/mysql-queries.php; in the left panel click
on 'Aggregates' then on 'Within-group aggregates'.
BTW your id columns should be ints not doubles, bigints if you expect
huge rowcounts. Likewise status.
PB
-----
David Ruggles wrote:
I'm new to this list, but have searched through the archives and haven't
found anything that addresses my question.
First a little background:
I'm creating a supply ordering system. Each supply order may be modified
many times before being approved and finally filled.
I created the following databases:
Supplyorder
uid double (auto) (pk)
site double
date date
Supplyorderhistory
uid double (auto) (pk)
supplyorderuid double
status double
Supplyorderlineitems
uid double (auto) (pk)
supplyorderhistoryuid double
other fields ...
Each time an existing supply order is modified a new history record is
created so we have a paper trail of all the changes made to the order and
the specific line items ordered are associated with the history record.
I want to select for a given date all sites that have the last history
record with a certain status.
This will select the last history record for each site:
SELECT MAX(h.uid), site FROM supplyorder AS s, supplyorderhistory AS h
WHERE h.supplyorderuid = s.uid AND date = '2007-01-08' GROUP BY s.uid
But if I do this:
SELECT MAX(h.uid), site FROM supplyorder AS s, supplyorderhistory AS h
WHERE h.supplyorderuid = s.uid AND date = '2007-01-08' and status = '2'
GROUP BY s.uid
Max just becomes the last history record with a status of 2. I understand
why it is working this way and that it is working correctly, I just don't
know how to get what I want out of it.
Thanks in advance for any help.
Thanks,
David Ruggles
CCNA MCSE (NT) CNA A+
Network Engineer Safe Data, Inc.
(910) 285-7200 [EMAIL PROTECTED]
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.410 / Virus Database: 268.16.10/624 - Release Date: 1/12/2007
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
