a SQL quiz

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 - 17:02:34 CDT