By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,995 Members | 1,090 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,995 IT Pros & Developers. It's quick & easy.

Retrieve Unique records from table whilst ignoring duplicates

P: 45
I am trying to remove any duplicate value and display just the unique values using the select query below. I need the select query to display just the distinct values based on Member.ID

SELECT MEMBER.ACCT, MEMBER.NACD, MEMBER.FRST, MEMBER.ID, MEMBER.LAST, MEMBER.MID, ORDER.BAL, ORDER.TYPE, ORDER.TINUM
FROM MEMBER INNER JOIN ORDER ON MEMBER.ACCT = ORDER.SHACCT
WHERE (SELECT DISTINCT MEMBER.ID FROM MEMBER) AND ((ORDER.BAL)>0) AND ((ORDER.TINUM)=1);

NOTE: ID IS NOT A PRIMARY KEY BUT IT'S A UNIQUE NUMBER TO EACH RECORD
Mar 16 '10 #1
Share this Question
Share on Google+
6 Replies


code green
Expert 100+
P: 1,726
NOTE: ID IS NOT A PRIMARY KEY BUT IT'S A UNIQUE NUMBER TO EACH RECORD
I don't understand. How can the id be UNIQUE if there are duplicates?

But the principle of finding duplicates is
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM table WHERE id IN (
  2.    SELECT id FROM table GROUP BY id HAVING count(id >1)
Well this is one way
Mar 16 '10 #2

ck9663
Expert 2.5K+
P: 2,878
You have two tables. Which one has duplicates and which one has the duplicate? I know ORDERS might have 1-N relationship with MEMBER. But it could also have actual duplicate records. That's why am asking.

~~ CK
Mar 16 '10 #3

P: 45
In the member table I can pull up the following information

2345 THOMAS ADAMS 34687921
7321 HERMAN CORP 34687921
4323 JOE TORY TOM 34687921


34687921 is stored in the member table; I need a query to retrieve only one of the records with containing 34687921(which is the Member.ID row) it does not matter which one it retrieves
Mar 16 '10 #4

ck9663
Expert 2.5K+
P: 2,878
Try this pseudo-code

Expand|Select|Wrap|Line Numbers
  1.  
  2. with dist_members (memberId)
  3. as
  4. (
  5. select distinct id from members
  6. )
  7. select memberid, o.columns...
  8. from dist_members m
  9. inner join orders o on m.memberd = i.id
  10.  
  11.  
Happy Coding!!!

~~ CK
Mar 16 '10 #5

nbiswas
100+
P: 149
Assuming you are using Sql server 2005+

The table information is as under

Expand|Select|Wrap|Line Numbers
  1. Id            Names                             DuplicateFields
  2. ------------------------------------------------------------------------------
  3. 2345     THOMAS ADAMS              34687921 
  4. 7321     HERMAN CORP                 34687921 
  5. 4323    JOE TORY TOM                 34687921
Query:
Expand|Select|Wrap|Line Numbers
  1. Select Id,Names,DuplicateFields From(
  2. Select Dense_Rank() Over(Partition by Duplicatefields Order by Id)x,Id,Names,DuplicateFields from tblInfo)y
  3. where y.x = 1
The output will be

Expand|Select|Wrap|Line Numbers
  1. Id            Names                             DuplicateFields
  2. ------------------------------------------------------------------------------
  3. 2345     THOMAS ADAMS              34687921 
Mar 25 '10 #6

P: 13
WHERE (SELECT DISTINCT MEMBER.ID FROM MEMBER) AND ((ORDER.BAL)>0) AND ((ORDER.TINUM)=1);

what does that ,where is the "in" condition
Mar 25 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.