Re: GROUP BY and join

"Guido Villa" <piribillo_at_yahoo.it> wrote in message news:96ea3b81.0207160111.df6c2b9_at_posting.google.com...
> 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

SELECT ls.date, ls.itemcode, i.desc
FROM items i, sales ls
where ls.date =
(SELECT MAX(date)
 FROM sales x
WHERE x.itemcode = ls.itemcode
);

You are not using a subquery you are using an *inline view*! And in this particular case there is no need to use inline views at all. Please note you are using the reserved word date in your table definition.

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address