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
-----Original Message-----
Sent: Tuesday, September 23, 2003 1:40 PM
To: Multiple recipients of list ORACLE-L
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Alan Gano
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).
