Thanks! I'll make the column type change. Thanks,
David Ruggles CCNA MCSE (NT) CNA A+ Network Engineer Safe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -----Original Message----- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Friday, January 12, 2007 3:42 PM To: David Ruggles Cc: 'mysql' Subject: Re: Selecting records based on max and value 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]
