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

Getting Distinct Values From A Single Row

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
Expand|Select|Wrap|Line Numbers
  1. SELECT MEMBER.ACCT, MEMBER.NACD, MEMBER.FRST, MEMBER.ID, MEMBER.LAST, MEMBER.MID, ORDER.BAL, ORDER.TYPE, ORDER.TINUM
  2. FROM MEMBER INNER JOIN ORDER ON MEMBER.ACCT = ORDER.SHACCT
  3. 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 15 '10 #1

✓ answered by 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.
Expand|Select|Wrap|Line Numbers
  1. SELECT   First(tM.ACCT) AS Acct,
  2.          First(tM.NACD) AS NACD,
  3.          First(tM.FRST) AS First,
  4.          tM.ID
  5.          First(tM.LAST) AS Last,
  6.          First(tM.MID) AS Mid,
  7.          First(tO.BAL) AS Bal,
  8.          First(tO.TYPE) AS Type,
  9.          1 AS TINum
  10.  
  11. FROM     MEMBER AS tM INNER JOIN ORDER AS tO
  12.   ON     tM.ACCT=tO.SHACCT
  13.  
  14. WHERE    ((tO.BAL>0)
  15.   AND    (tO.TINUM=1))
  16.  
  17. GROUP BY tM.ID;

Share this Question
Share on Google+
14 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 16 '10 #2

P: 45
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??
Mar 16 '10 #3

100+
P: 109
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...
Mar 16 '10 #4

NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 16 '10 #5

NeoPa
Expert Mod 15k+
P: 31,186
@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.
Mar 16 '10 #6

100+
P: 109
Remember, many of our members are very inexperienced at this level. Most progress over time.
helloooooooo!
*waves*
Mar 16 '10 #7

NeoPa
Expert Mod 15k+
P: 31,186
And you're a perfect example of course :D
Mar 16 '10 #8

P: 45
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
Mar 16 '10 #9

100+
P: 109
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?
Mar 16 '10 #10

P: 45
Just one record based on the Member.ID
Mar 16 '10 #11

P: 45
Just one record based on the Member.ID it can be any record
Mar 16 '10 #12

NeoPa
Expert Mod 15k+
P: 31,186
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT   First(tM.ACCT) AS Acct,
  2.          First(tM.NACD) AS NACD,
  3.          First(tM.FRST) AS First,
  4.          tM.ID
  5.          First(tM.LAST) AS Last,
  6.          First(tM.MID) AS Mid,
  7.          First(tO.BAL) AS Bal,
  8.          First(tO.TYPE) AS Type,
  9.          1 AS TINum
  10.  
  11. FROM     MEMBER AS tM INNER JOIN ORDER AS tO
  12.   ON     tM.ACCT=tO.SHACCT
  13.  
  14. WHERE    ((tO.BAL>0)
  15.   AND    (tO.TINUM=1))
  16.  
  17. GROUP BY tM.ID;
Mar 16 '10 #13

P: 45
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 :)
Mar 17 '10 #14

NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 17 '10 #15

Post your reply

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