Home » SQL & PL/SQL » SQL & PL/SQL » Getting Unique Records from the table
Getting Unique Records from the table [message #241085] Mon, 28 May 2007 05:24 Go to next message
pratap.babar
Messages: 4
Registered: May 2007
Location: India
Junior Member

Hi

i have table in which there are 10 records and in which there are records which have the same entries in the table . there are such 3 records in the table .
the table is like this

custname custid custcity
Pratap 1058 developer
Pratap 1058 developer
Pratap 1058 developer
Prashant 1097 developer
Prashant 1097 developer
Prashant 1097 developer
Pratik 1085 developer
Pratik 1085 developer
Pratik 1085 developer
mahesh 1026 developer


and from this table i want the records with the distinct custid .

means only 4 records to be in the output are
Pratap 1058 developer
Prashant 1097 developer
Pratik 1085 developer
mahesh 1026 developer


suggest me the appropriate query for it ?

[Updated on: Mon, 28 May 2007 05:25]

Report message to a moderator

Re: Getting Unique Records from the table [message #241095 is a reply to message #241085] Mon, 28 May 2007 05:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use distinct keyword: select distinct ...

Regards
Michel
Re: Getting Unique Records from the table [message #241097 is a reply to message #241095] Mon, 28 May 2007 05:49 Go to previous messageGo to next message
pratap.babar
Messages: 4
Registered: May 2007
Location: India
Junior Member

yes i had used that query for getting the records but we can use the distinct keyword with the one column only and

the query should be like this

select distinct custid from Customer ;which gives me only distinct custid from the table but unable to get the description of other columns so i want to know the query which gives me the oteh cloumn details for it also


so please guide me in builting that query ..
Re: Getting Unique Records from the table [message #241099 is a reply to message #241097] Mon, 28 May 2007 05:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you have different column values with the same custid?

Regards
Michel
Re: Getting Unique Records from the table [message #241104 is a reply to message #241085] Mon, 28 May 2007 06:02 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Can't understand why you can't use DISTINCT
Is this what you want?
scott@DBASE-SQL>>select * from try order by ename;

     EMPNO ENAME
---------- ----------
      7499 ALLEN
      7499 ALLEN
      7499 ALLEN
      7499 ALLEN
      7902 KING
      7369 SMITH
      7369 SMITH
      7369 SMITH
      7369 SMITH
      7521 WARD
      7521 WARD
      7521 WARD
      7521 WARD

13 rows selected.

scott@DBASE-SQL>>select distinct empno, ename from try;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7902 KING
icon7.gif  Re: Getting Unique Records from the table [message #241113 is a reply to message #241104] Mon, 28 May 2007 06:22 Go to previous messageGo to next message
pratap.babar
Messages: 4
Registered: May 2007
Location: India
Junior Member

Thank you very much


it solved my problem ....
Re: Getting Unique Records from the table [message #241236 is a reply to message #241085] Mon, 28 May 2007 23:43 Go to previous messageGo to next message
iram_11181
Messages: 3
Registered: May 2007
Junior Member
create Table p_temp as select * from (select bill_ref_no,count(bill_ref_no) b ,prep_date from bill_image group by bill_ref_no,prep_date
order by bill_ref_no) where b>1 order by prep_date;


CREATE TABLE P_MAST AS

SELECT DISTINCT * FROM BILL_IMAGE WHERE BILL_REF_NO IN (SELECT BILL_REF_NO FROM p_temp)


delete from BILL_IMAGE WHERE BILL_REF_NO IN (SELECT BILL_REF_NO FROM p_temp);
commit;
insert into BILL_IMAGE select * from P_mast;
commit;

try this
Re: Getting Unique Records from the table [message #241245 is a reply to message #241236] Mon, 28 May 2007 23:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try this for what?

Regards
Michel
Re: Getting Unique Records from the table [message #241248 is a reply to message #241245] Tue, 29 May 2007 00:07 Go to previous messageGo to next message
iram_11181
Messages: 3
Registered: May 2007
Junior Member
try using the logic that i have giving in your tables nd respective fields of your table
Re: Getting Unique Records from the table [message #241269 is a reply to message #241248] Tue, 29 May 2007 01:15 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What did you try to show with your post/script?
What did the OP ask?
Does both fit?

Regards
Michel
Previous Topic: How to execute a query as parameter/variable in PLSQL block
Next Topic: Problem: ORA-00980: synonym translation is no longer valid
Goto Forum:
  


Current Time: Wed Dec 07 07:03:51 CST 2016

Total time taken to generate the page: 0.11264 seconds