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