Hi David,
David Ruggles wrote:
<snip>
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.
<snip>
David Ruggles
CCNA MCSE (NT) CNA A+
Network Engineer Safe Data, Inc.
(910) 285-7200 [EMAIL PROTECTED]
This takes two steps. First we find the last histories:
CREATE TEMPORARY TABLE tmpLastChanges
SELECT MAX(h.uid) max_id, h.supplyorderuid FROM supplyorderhistory AS h
WHERE date = '2007-01-08' GROUP BY h.supplyorderuid;
Then use that list to get the actual records you seek:
SELECT max_id, site
FROM tmpLastChanges tlc
INNER JOIN supplyorderhistory h
on tlc.max_id = h.uid
AND h.status = 2
INNER JOIN supplyorder AS s,
WHERE h.supplyorderuid = s.uid;
This is just one way to implement a "group-wize maximum" query pattern. Search
the list and the rest of the web for this term and you will see many other variations.
--
Shawn Green, Support Engineer
MySQL Inc., USA, www.mysql.com
Office: Blountville, TN
Are you MySQL certified? www.mysql.com/certification
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ /
/ /|_/ / // /\ \/ /_/ / /__
/_/ /_/\_, /___/\___\_\___/
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
