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 david@stripped
>
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
