GROUP BY and join

Hi everybody.

I have a problem, which I have solved already, but I would like to submit it to you to see wether there is a better solution or not.

I have two tables.
[SALES]

id date itemcode qty reseller

1  01/15/01     A     15  john
2  05/07/01     A      3  john
3  09/11/01     A      7  dave
4  03/09/01     B      9  dave
5  03/22/01     B      4  clint
6  06/12/01     C     22  mike

[ITEMS]

itemcode desc authorized_reseller

A         cd     john
B         book   dave
C         video  mike

The first problem I have is that I would like to get the itemcode, the description and the date for the last sale of every item. Note that I do not need the quantity or the sale id: the only thing I need (aside from the date, which is in a MAX(), and the itemcode, which is in the GROUP BY) is the description, which is in a table where itemcode is the key.
Does this make any difference from the usual problem with group by and correlated rows?

If not, the solution should be:

SELECT ls.maxdate, ls.itemcode, i.desc FROM items i, (SELECT MAX(date) maxdate, itemcode FROM sales GROUP BY itemcode) ls WHERE ls.itemcode = i.itemcode;

which gives:

maxdate itemcode desc

09/11/01     A    cd
03/22/01     B    book
06/12/01     C    video

The real problem arises when I want to get only the sales made by authorized resellers. I would like to get

maxdate itemcode desc

05/07/01     A    cd
03/09/01     B    book
06/12/01     C    video

How can I do? I have these two solutions, but I don't like them very much, also because in the real query I have to get a lot of fields as the descriptions.

SELECT MAX(s.date) maxdate, s.itemcode, i.desc FROM items i, sales s WHERE s.itemcode = i.itemcode
GROUP BY s.itemcode, i.desc;

SELECT MAX(s.date) maxdate, s.itemcode, MIN(i.desc) FROM items i, sales s
WHERE s.itemcode = i.itemcode
GROUP BY s.itemcode;

Is there any other viable way to do this? Which is (or should be) the fastest one? I could do that with the subquery of the first example, for instance, but this would require an additional join, and I think this could slow doen the query...

Thanks all very much in advance for any help

Bye

Guido Received on Tue Jul 16 2002 - 04:11:15 CDT