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

How to get multiple row values having distinct column value

P: 33

I have table 'patientdata' consisting of patient data in three columns fname, lname and loc.

I want to have a query that shows which of these patients were diagnosed in more than one location.

Here is sample table:

fname lname loc
tom hanks mi
tom hanks ca
jon trevolta nj
jon trevolta nj
tom hanks mi
jim kerry md

Answer should be:

tom hanks mi
tom hanks ca

Thanks in advance

Aug 2 '11 #1
Share this Question
Share on Google+
10 Replies

Expert Mod 10K+
P: 12,364
You could use a count distinct query that groups by the first name and last name. Anything having a count greater than 1 would be the results you're looking for. If you absolutely have to have the locations as well, then you could subquery it in the from clause.
Aug 2 '11 #2

P: 33
Is there any way I can accomplish this in one single query
Aug 2 '11 #3

P: 33
And here is the thing I dont want distinct fname or lname but I want distinct loc for same names
Aug 2 '11 #4

Expert Mod 10K+
P: 12,364
Yes you can do it in a single query, my post lays out how to do that.

Yes, I know. That's why you do a count distinct on loc.
Aug 2 '11 #5

P: 33
I added one more column dob. When I use the below command... it result as

Expand|Select|Wrap|Line Numbers
  1. tom    hanks            mi    10    2
  2. jon    trevolta    nj    13    2

and not

Expand|Select|Wrap|Line Numbers
  1. tom    hanks            mi    10    
  2. tom     hanks           md      10
  3. jon    trevolta    nj    13    
  4. jon    trevolta    nj    13    
Can you suggest me how to get this.. And thanks for your help so far

Expand|Select|Wrap|Line Numbers
  1. SELECT *, 
  2. COUNT(dob) AS NumOccurrences
  3. FROM patientdata
  4. GROUP BY dob
  5. HAVING COUNT(dob) > 1
Aug 2 '11 #6

Expert Mod 10K+
P: 12,364
That query can't possibly work. It should error out seeing as how you have a last name field and first name field that is not in an aggregate function nor in the group by clause.

Assuming dob is date of birth, why are you counting date of birth? I thought you wanted to count distinct locations.

And as I said earlier, if you want to return the location as well, you would need to subquery the aggregate query. Either into the from clause so you can join by it, or into the where clause so you can filter by it.
Aug 2 '11 #7

P: 33
I wanted to do this is in single query which is a priority.
Also I found out that two person can have same name and so filtering through dob is much better option... So what change I should do to query as I don't want to use subquery...

Again thanks
Aug 2 '11 #8

P: 33
Hey I got it.. Without using subquery. As if ur using count its expensive with large # of data instead use this.

Expand|Select|Wrap|Line Numbers
  1. SELECT  *
  2. FROM    patientdata mto
  4.         (
  5.         SELECT  1
  6.         FROM    patientdata mti
  7.         WHERE   mti.dob = mto.dob
  8.         LIMIT 1, 1
  9.         )
Aug 2 '11 #9

Expert Mod 10K+
P: 12,364
You just used a subquery. Plus I don't see how that is going to return the results you need. That subquery is going to return exist for every record so you're just going to get everything back.
Aug 3 '11 #10

P: 1
can this be a solution

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT pd1.Firstname, pd1.Lastname, pd1.Diagnosis_location, Medical_record_number
  2. FROM patientdata pd1 JOIN patientdata pd2
  3. USING(Medical_record_number)
  4. WHERE pd1.Firstname = pd2.Firstname
  5.     AND pd1.Lastname = pd2.Lastname
  6.     AND pd1.Date_of_Birth = pd2.Date_of_Birth
  7.     AND pd1.Diagnosis_location != pd2.Diagnosis_location
  8. ORDER BY pd1.Lastname;
Dec 29 '11 #11

Post your reply

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