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 <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
