Getting Distinct Values From A Single Row

[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

[NeoPa]

You probably want SELECT DISTINCT in line one.

If that's not what you want, then you need to ask the question more clearly, as this doesn't make much sense.

[toadmaster]

I tried it with Select Distinct when I initially started the query but it does not provide the unique records I am after.

Basically what I am trying to achieve is; from the member table retrieve the rows listed but in the list being retrieved select only the distinct records based on the Member.ID ( kind of like in excel when you remove or delete duplicate records based on a certain column). The Member.ID may be associated with multiple records but I want to get rid of the duplication and display just one unique record of the member.id row.

I hope my explaination makes sense??

[hedges98]

If Member.ID is associated with multiple records then how can it display one unique record as some of the fields will have multiple values, surely? Unless I'm missing something blindingly obvious...

[NeoPa]

Maybe if you posted some example input and required output data it may become clearer what you're after. I suspect we may be talking about a GROUP BY clause in place of the DISTINCT predicate here, but there's simply too little information to be sure.

[NeoPa]

@hedges98
I doubt it's you Hedges. Let's see what the OP comes up with in the way of example data and maybe the request will make more sense.

Remember, many of our members are very inexperienced at this level. Most progress over time.

[hedges98]

Remember, many of our members are very inexperienced at this level. Most progress over time.

helloooooooo!
*waves*

[NeoPa]

And you're a perfect example of course :D

[toadmaster]

The following is a sample of the data I am getting; you will notice that based on the primary keys in this case 2345, 7321 and 4323 there are three records being retrieved by the query. What I want it to retrieve is just one of the records based on the Member.ID in this case 34687921 (this is not the primary key)

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

Currently I am having to export the results to excel and then delete the duplicates based on the Member.ID row.

thanks for all your help

[hedges98]

So what is the desired output you are looking for? Is there a particular record you want to display based on Member.ID or do you just want any one record to be displayed?