select a.item, a.month, a.price
from temp a
where (a.item, a.month) in (select item, max(month) from temp group by
tem )
BM Lam <1116-530_at_online.de> wrote in message
<373F4079.27DE1FFE_at_online.de>...
>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 Sun May 16 1999 - 21:27:34 CDT
