Chandan, I don't know if you had wanted to select only one column with unique values in it [if so, what I suggested earlier would and should work]. If you want to select the whole rows but once only for each unique value in the one particular column then watch your 'criteria of selection' because what you write in the sub-query is really going to determine what else would be returned to you besides the column you are selecting unique values from.
You may end up getting
--------------------------
reg_nbr cust_id
------- ---------
100 sd
200 er
--------------------------
or, you may end up getting
--------------------------
reg_nbr cust_id
------- ---------
200 er
300 sd
--------------------------
You really need to be careful in deciding what exactly are you looking for. A SQL like
SELECT DISTINCT col2, col1, col3, col4
FROM tablename;
is going to return you all rows with distinct values for col2 + values for col1, col3, col4 which lived in the first appearing row for each distinct value of col2.
Gurmohan
www.onlymath.com - The place for learning and teaching math.
-----Original Message-----
From: Vijay Kumar
[mailto:oracledba-ezmlmshield-x28721690.[Email address protected]
Sent: Friday, October 28, 2005 12:17 PM
To: LazyDBA Discussion
Subject: RE: How to Select the Unique records
Hi Chandan,
In my opinion, distinct clause may not solve your duplicate issue. However if you try with following query, you might get unique values:
select * from testtable where rowid not in(select min(rowid) from testtable)
group by cust_id, reg_nbr
Regards,
Vijay
-----Original Message-----
From: Chandra Guptan Bose [mailto:oracledba-ezmlmshield-x79494709.[Email address protected]
Sent: 28 October 2005 16:52
To: LazyDBA Discussion
Subject: How to Select the Unique records
Hi all,
I have duplicates in one table where the required field is not PK bu i
want the unique records in that
reg_nbr cust_id
--------- ----------
100 sd
200 er
300 sd
i want unique records on cust_id as it goes as a PK in the target table.
Chandra Guptan Bose,
Software Engineer,
Charles Schwab ,
Datawarehousing Center,
iGATE Global Solutions
Bangalore
Ph No : +91-80-51040000 Extn: 5382
Mobile : +91-98863-97793
Web : http://chandraguptan.250free.com
<http://chandraguptan.250free.com/>
Email : [Email address protected] <mailto:[Email address protected]
Where There's A Will There's A Way...
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Information transmitted by this EMAIL is proprietary to iGATE Group of Companies and is intended for use only by the individual
or entity to whom it is addressed and may contain information that is privileged, confidential, or exempt from disclosure under
applicable law. If you are not the intended recipient of this EMAIL immediately notify the sender at iGATE or [Email address protected]
and delete this EMAIL including any attachments
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html
________________________________________________________________________
This e-mail message (including any attachment) is intended only for the personal
use of the recipient(s) named above. This message is confidential and may be
legally privileged. If you are not an intended recipient, you may not review, copy or
distribute this message. If you have received this communication in error, please notify
us immediately by e-mail and delete the original message.
Any views or opinions expressed in this message are those of the author only.
Furthermore, this message (including any attachment) does not create any legally
binding rights or obligations whatsoever, which may only be created by the exchange
of hard copy documents signed by a duly authorised representative of Hutchison
3G UK Limited..
________________________________________________________________________
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page