Re: a SQL quiz

What you need to use in this situation is a correlated subquery. A correlated subquery is a query that references columns in the table of its containing query:

select item, month, price
from my_table a
where month =
 (select max(month)
   from my_table b
    where a.item = b.item)

I tested this code with your data, and it produces the results you want. In effect, the table is joined to itself as part of an internal looping process.

I understand, however, that using a correlated subquery with a large table can be a performance hog because of the looping it causes (I have never used it with a "large" table). If you have a big table, you may have to solve the problem in steps.

HTH,
Michael

BM Lam wrote:

> suppose there is a table with such content:
>
> item month price
> --------------
> c 5 8
> b 3 7
> a 2 5
> a 3 3
> b 2 7
> b 6 3
>
> and you want to select only one row per item wher the row with the
> highest month is the candidate, i.e, the result set should be:
>
> item month price
> --------------
> c 5 8
> a 3 3
> b 6 3
>
> Intuitively, I think this should be feasible with a standard SELECT
> statement, using something like a GROUP BY clause, without getting into
> a procedural SQL language as PL/SQL or Transact SQL. But how do I get
> the syntax right?
>
> SELECT item, price, max(month) from my_table
> GROUP BY item, price
>
> on a Oracle system actully returns all the 6 rows. That is not what was
> intended. Does some one have a better idea?
>
> Thanks for any hints
>
> ------------------------------------------------------------------------
>
> Bon-Minh Lam <bmlam_at_online.de>
>
> Bon-Minh Lam
> <bmlam_at_online.de>
> HTML Mail
> Netscape Conference Address
> Netscape Conference DLS Server
> Additional Information:
> Last Name Lam
> First Name Bon-Minh
> Version 2.1
Received on Sun May 16 1999 - 20:04:24 CDT