From: Pooly
Date: June 21 2006 8:57am
Subject: Re: a tricky join
List-Archive: http://lists.mysql.com/mysql/199120
Message-Id: <8db58d6b0606210157o11703f3u8f4e8fb97390b3f5@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,


2006/6/21, Helen M Hudson <helen@stripped>:
> Yes, I can see how this would work for just the one order and hardcoding the
> 100... but I cannot assume only to sum distinct values and my table has
> other order_refs in it with the same multiple rows of over multiple days, so
> I need a more generic select that will list this nice summary for all
> orders... do you see what I mean?
>
> e.g.
>  id | date   | order_ref | amount
>  1  | 1/1/01 | 100       | 1000 << these 2 are the rows
>  2  | 1/1/01 | 100       | 200  << i want to exclude for order 100
>  3  | 2/1/01 | 100       | 1000
>  4  | 2/1/01 | 100       | 200
>  5  | 2/1/01 | 100       | 50
>  6  | 2/1/01 | 101       | 10000 << i also need to exclude these 2 rows
>  7  | 2/1/01 | 101       | 2000  << out of the calculation for order 101
>  8  | 2/1/01 | 101       | 10000
>  9  | 3/1/01 | 101       | 2000
> 10 | 3/1/01 | 101       | 500

What you're asking does not involve join, but is a trick called
group-wise maximum.
Depending on your version of MySQL, there are several options to resolve this :
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html

(I haven't tested it) :
SELECT date, order_ref, sum(amount)
FROM   table s1
WHERE  date=(SELECT MAX(s2.date)
              FROM table s2
              WHERE s1.order_ref= s2.order_ref)
GROUP BY s1.order_ref;

the subquery get you the maximum date for each order_ref, and then you
do the sum of this date. Was it what you were looking for ?

-- 
http://www.w-fenec.org/