List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:January 12 2007 8:41pm
Subject:Re: Selecting records based on max and value
View as plain text  
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	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

Thread
How to pronounce MyISAM and InnoDBjs 7 Jan
  • Re: How to pronounce MyISAM and InnoDBMichael Stearne7 Jan
  • Re: How to pronounce MyISAM and InnoDBJan Pieter Kunst7 Jan
    • Re: How to pronounce MyISAM and InnoDBPhilip Mather8 Jan
  • Re: How to pronounce MyISAM and InnoDBChris White8 Jan
Re: How to pronounce MyISAM and InnoDBBrian Dunning7 Jan
  • [OT} How to pronounce GIF (was: Re: How to pronounce MyISAMand InnoDB)TK8 Jan
    • Re: [OT} How to pronounce GIF (was: Re: How to pronounce MyISAM and InnoDB)Brian Dunning12 Jan
      • RE: [OT} How to pronounce GIF (was: Re: How to pronounce MyISAM and InnoDB)Jerry Schwartz12 Jan
      • Re: [OT} How to pronounce GIFGerald L. Clark12 Jan
        • Re: [OT} How to pronounce GIFmizioumt19 Jan
RE: [OT} How to pronounce GIFJohn Trammell12 Jan
  • Selecting records based on max and valueDavid Ruggles12 Jan
    • Re: Selecting records based on max and valuePeter Brawley12 Jan
      • RE: Selecting records based on max and valueDavid Ruggles12 Jan
    • Re: Selecting records based on max and valueShawn Green13 Jan
  • Re: [OT} How to pronounce GIFEric Braswell12 Jan