Re: a SQL quiz

Another solution using group by:

select

   item,
   month,
   price
from

   my_table
group by

   item
having

   month = max(month)

--Andy

In article <373F4079.27DE1FFE_at_online.de>, BM Lam <1116-530_at_online.de> 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
>
Received on Mon May 17 1999 - 15:03:10 CDT