Hi,
2006/6/21, Helen M Hudson <[EMAIL PROTECTED]>:
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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
