RE: select distinct values

Man, every time I think I have a handle on the analytic functions, someone bowls me over with it's power and simplicity. I was trying to do this using the FIRST function of 9i. Oh well.

INSERT INTO saved_messages ...

Rich

Rich Jesse                           System/Database Administrator
rjesse_at_qtiworld.com                  Quad/Tech Inc, Sussex, WI USA

> -----Original Message-----
> From: elain he [mailto:elainhe_at_hotmail.com]
> Sent: Tuesday, September 23, 2003 5:00 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: select distinct values
>
>
> Alan,
> Thanks! that works.
>
> elain
>
>
> >From: Alan Gano <Alan.Gano_at_banfield.net>
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Subject: RE: select distinct values
> >Date: Tue, 23 Sep 2003 13:04:47 -0800
> >
> >
> >Try this (in 8i or above):
> >
> >select * from (
> > select
> > testid,
> > name,
> > status,
> > rank() over(
> > partition by testid
> > order by rownum
> > ) dup_rank
> > from testing
> >)
> >where dup_rank = 1

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).