Re: a SQL quiz

From: David Pattinson <david_at_addease.com.au>
Date: Mon, 17 May 1999 09:53:20 +1000
Message-ID: <373F5A70.6B222730@addease.com.au>

Try something like the following. I haven't run the query, but this is the general idea:

--SYBASE SYNTAX--
--I think, as I usually use SQL-Anywhere
--Alternatively you could extract the set of item,max(month) pairs into
--a temporary table and join it to my_table on item and month.

SELECT a.item, a.price, a.month
FROM my_table AS a
WHERE a.month = (SELECT max(month)

                                FROM my_table
                                WHERE my_table.item = a.item)

;

--ORACLE SYNTAX--
SELECT a.item, a.price, b.max_month
FROM my_table a, (select item, max(month) max_month

                                from my_table
                                group by item) b
WHERE a.item = b.item
            and a.month = b.max_month

;

Table b will have only one row per item, with the maximum month value as max_month. The join on item, month ensures that the correct price will be returned.

HTH, David.

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
Received on Sun May 16 1999 - 18:53:20 CDT