RE: Selecting records based on max and value - David Ruggles - com.mysql.lists.mysql

17 messages in com.mysql.lists.mysqlRE: Selecting records based on max an...

From	Sent On	Attachments
js	07 Jan 2007 07:08
Michael Stearne	07 Jan 2007 07:15
Brian Dunning	07 Jan 2007 10:15
Jan Pieter Kunst	07 Jan 2007 11:24
TK	07 Jan 2007 16:23
Philip Mather	08 Jan 2007 01:37
Chris White	08 Jan 2007 08:36
Brian Dunning	12 Jan 2007 10:48
Jerry Schwartz	12 Jan 2007 11:01
Gerald L. Clark	12 Jan 2007 11:04
John Trammell	12 Jan 2007 11:47
Eric Braswell	12 Jan 2007 12:09
David Ruggles	12 Jan 2007 12:09
Peter Brawley	12 Jan 2007 12:41
David Ruggles	12 Jan 2007 12:46
Shawn Green	12 Jan 2007 22:07
mizi...@netscape.net	19 Jan 2007 08:28

Subject:	RE: Selecting records based on max and value
From:	David Ruggles (dav...@safedatausa.com)
Date:	01/12/2007 12:46:06 PM
List:	com.mysql.lists.mysql

Thanks! I'll make the column type change.

Thanks,

-----Original Message----- From: Peter Brawley [mailto:pete...@earthlink.net] 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.

-----

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,