List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:January 13 2007 6:07am
Subject:Re: Selecting records based on max and value
View as plain text  
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

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