Re: select distinct values

From: elain he <elainhe_at_hotmail.com>
Date: Tue, 23 Sep 2003 13:19:44 -0800
Message-ID: <F001.005D0DA1.20030923131944@fatcity.com>

Daniel,
I need only 1 row for each testid, name,status combination. I can do a distinct on testid but I need the name and status to be displayed as well.

thanks.

elain

>From: Daniel Fink <Daniel.Fink_at_Sun.COM>
>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 12:54:50 -0800
>
>Elain,
>
>The query is returning the correct data. DISTINCT applies to the row as a
>whole. Since the NAME adds a new element, DISTINCT finds that 1MIKEY is
>different than 1JOEY.
>
>How do you determine the 'first' row of the testid? Depending upon the
>access path (table/index), this could be a different row that what you
>expect. Do you only care about 1 row for each TESTID and
>STATUS combination?
>
>Daniel
>
>elain he wrote:
>
> > Hi,
> > Could someone shed some light on this.
> >
> > I'm trying to formulate a query to return distinct value on a column -
> > testid.
> >
> > select * from testing;
> > TESTID NAME STATUS
> > ---------- -------------------- -----------
> > 1 MIKE Y
> > 1 JOE Y
> > 1 JIM Y
> > 2 AMY Y
> >
> > The output I'm expecting is
> > TESTID NAME STATUS
> > ---------- -------------------- -----------
> > 1 MIKE Y
> > 2 AMY Y
> >
> > The query should display the first occurence of the testid and ignore
> > records with the same testid.
> >
> > thanks!
> >
> > elain
> >
> > _________________________________________________________________
> > Instant message in style with MSN Messenger 6.0. Download it now FREE!
> > http://msnmessenger-download.com
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: elain he
> > INET: elainhe_at_hotmail.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).
><< daniel.fink.vcf >>

---

Share your photos without swamping your Inbox. Get Hotmail Extra Storage today! http://join.msn.com/?PAGE=features/es

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: elain he
  INET: elainhe_at_hotmail.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).

Received on Tue Sep 23 2003 - 16:19:44 CDT