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/
