Hello all, I've got this table in a database at work, and unfortunately I'm just a temp hire so there's no persuading them to switch off access 2002 databases and move to something like sql2000 or mysql. but this is irrelevant.


I've got a table like so:

r=region
s=sku
p=period
a=amount

r s p a
1 1 2 1
1 1 1 3
1 1 3 2
1 2 3 2
1 2 2 5
1 2 3 4
2 1 .....

what I need is for each region+sku combination (so like

"SELECT DISTINCT region, sku FROM table GROUP BY region, sku"

but this is not actually my code) I need to find the record that shipped most recently (max(period)) and then within that, I need to find the most shipment (max(amount))

the question is how do I do this? I can first do a query to

"SELECT DISTINCT region, sku, max(period) FROM table GROUP BY region, sku"

and this gets my max(period), but I have to drop the amount column for this to work, because puttingin max(amount) would pull out the max amount for ALL region+sku combinations, not the max(amount) within the max(period).

I can include amount in my field selection but sql requires for a group by that if I'm not using an aggregate function on a column, but I want to select it, it has to be listed in the group by fields. This can't happen because grouping by amount prevent me from selecting the max period within the region+sku combination.

So then I could do it like so:

SELECT DISTINCT region,sku, max(amount), period FROM table GROUP BY region, sku, period

and this is my best so far, because it gets me the distinct periods within the region+sku combination and the max amount FOR each period ...

this is where I'm lost

I tried a parent query to select from this current subquery the max(period), but for obvious reasons I then have to drop my amount column again, because again group by requires I include amount in either an aggregate function or the group by conditions, and grouping by amount eliminates my ability to select the max(period)

I've had other ideas, but I've written enough, and I imagine someone should have a clue for me.

thanks so much, I'm very willing to provide more information

john h.

nevermind, I got it! right when I hit submit it came to me ....


select the distinct region+sku combos, and find the max period for that ... so we'd have one record per region+sku and the only other field would be the maxperiod ... then inner join that query with the original table ON r=r, s=s, and p=p .... this will then pull all the amounts for that max period, and we can just do the max amount for that distinct region+sku+period combo, and that leaves us with the max of the original four fields, and I can then do one more inner join with my original table where r=r, s=s, p=p, and a=a to pull all the other fields I didnt' tell you all about :-P

thanks to whoever wasted their time reading me blabber!

This should do it

SELECT p.region, p.sku, p.period, a.amount
FROM
(SELECT region, sku, MAX(period) period
FROM table
GROUP BY region, sku) p,
(SELECT region, sku, period, MAX(amount) amount
FROM table
GROUP BY region, sku, period) a
WHERE p.region = a.region
AND p.sku = a.sku
AND p.period = a.period;

Incidentally you don't need the distinct statments because the group by part returns distinct records.

Looks we crossed in the post. Hope that helped anyway.

brilliant, your code was a lot better ... mine was ... so many nested sql statements!

oh well, that's what happens when you have brain farts


thanks for your help, although ... when I read your post I'd already used my code to then insert the 9014 records either statement would produce into a different table ....

who says one-time-use code has to be efficient, right?

cheers, thanks