Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: a SQL quiz

Re: a SQL quiz

From: BM Lam <1116-530_at_online.de>
Date: Sat, 29 May 1999 13:51:18 +0200
Message-ID: <374FD4B5.4372FADB@online.de>


That does not work on my system. And stupid me, I have tested a working solution posted by OracleNet but forgot to save it on disk. And now I cant get syntax right again.

Does anyone remember the solution?

Andy Kraftsow schrieb:

> 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
> >

--
P.S: I am moving the e-mail account. Please use my new e-mail address as of now:

bmlam_at_online.de

May I also suggest that you update your address book accordingly if you are using one at all.


Received on Sat May 29 1999 - 06:51:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US