Helen M Hudson schrieb:
> Hi all
>
> I'm gradually learning how much simpler it is to do things with joins. I can tell
> that I haven't seen the light yet... but I'm expecting fireworks pretty soon when it all
> falls into place brain-wise and I can wallow in the joy of smaller more efficient sql!
>
> I'd really appreciate a little help with this one:
>
> I have an order database containing multiple rows for each order. The problem is
> that if there is further activity on an order in future days, the system feeding me
> resends the whole order again. So I'd like a way of excluding all previous instances of
> an order when I'm doing calculations.
>
> So, if my table structure was:
> 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
> 3 | 2/1/01 | 100 | 1000
> 4 | 2/1/01 | 100 | 200
> 5 | 2/1/01 | 100 | 50
>
> I'd like to end up with
> latest_date_on_order | order_ref | sum(amount)
> 2/1/01 | 100 | 1250
>
> I know its a one-liner for someone who has reached the light... so any help very much
> appreciated!
>
> Helen
Well...
MAX(date) AS latest_date_on_order, SUM(amount) FROM table GROUP BY date
is it that what you looked for?
Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
