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]

Unless I'm missing something blindingly obvious...

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?

[toadmaster]

Just one record based on the Member.ID

[toadmaster]

Just one record based on the Member.ID it can be any record

[NeoPa]

It's unfortunate that you weren't able to post all that I'd requested. I thought I'd made the request pretty simple. Never mind, I don't want to go round the houses again so I'll make some guesses as to what I would be seeing if you had.

It looks like you want the data selected from any output record from the whole group where Member.ID is the same.

SELECT   First(tM.ACCT) AS Acct,
         First(tM.NACD) AS NACD,
         First(tM.FRST) AS First,
         tM.ID
         First(tM.LAST) AS Last,
         First(tM.MID) AS Mid,
         First(tO.BAL) AS Bal,
         First(tO.TYPE) AS Type,
         1 AS TINum

FROM     MEMBER AS tM INNER JOIN ORDER AS tO
  ON     tM.ACCT=tO.SHACCT

WHERE    ((tO.BAL>0)
  AND    (tO.TINUM=1))

GROUP BY tM.ID;

[toadmaster]

Worked exactly the way I wanted it to, thank you so much for all your help. When I grow up I want to know SQL just like you :)

[NeoPa]

I'm pleased that helped.

I find SQL is one of those things that is simpler than people give it credit for. Sure I have a bit of experience, but the full list of clauses isn't that vast. It seems to be another one of those areas where people get intimidated.

Jump in. Have fun. I'm sure you'll be able to master it in a relatively short time. It's the database concepts and understanding that are probably more difficult to pick up for people not familiar with them.