RE: Selecting records based on max and value

[David Ruggles]

Thanks! I'll make the column type change.

Thanks,

David Ruggles
CCNA MCSE (NT) CNA A+
Network Engineer	Safe Data, Inc.
[phone]	david@stripped



-----Original Message-----
From: Peter Brawley [mailto:peter.brawley@stripped] 
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.
> [phone]	david@stripped
>
>
>
>   


-- 
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