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


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