Retrieve Unique records from table whilst ignoring duplicates

[toadmaster]

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

[code green]

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

SELECT * FROM table WHERE id IN (
   SELECT id FROM table GROUP BY id HAVING count(id >1)

Well this is one way

[ck9663]

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

[toadmaster]

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

[ck9663]

Try this pseudo-code

 
with dist_members (memberId)
as
(
select distinct id from members
)
select memberid, o.columns...
from dist_members m
inner join orders o on m.memberd = i.id
 
 

Happy Coding!!!

~~ CK

[nbiswas]

Assuming you are using Sql server 2005+

The table information is as under

Id            Names                             DuplicateFields
------------------------------------------------------------------------------
2345     THOMAS ADAMS              34687921 
7321     HERMAN CORP                 34687921 
4323    JOE TORY TOM                 34687921

Query:

Select Id,Names,DuplicateFields From(
Select Dense_Rank() Over(Partition by Duplicatefields Order by Id)x,Id,Names,DuplicateFields from tblInfo)y
where y.x = 1

The output will be

Id            Names                             DuplicateFields
------------------------------------------------------------------------------
2345     THOMAS ADAMS              34687921 

[holyzuou]

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

what does that ,where is the "in" condition