DISTINCT not working as expected

[barbarao]

Hi. I have a query that needs to pull all locations that have one score in a specific month/year (this combo will change monthly). (Some locations have multiple scores.) I thought a DISTINCT would do it but the results are the same as without the DISTINCT. I hope someone can assist. Thanks in advance.

SELECT DISTINCT 
                        dbo.PracticeTable.OPNumber AS [Practice ID], dbo.PracticeTable.PracticeName AS [Practice Name], 'Primary Care | Pediatrics' AS [Practice Type], 
                        dbo.PracticeTable.PracticeAddressLine1 AS [Address 1], dbo.PracticeTable.PracticeAddressLine2 AS [Address 2], dbo.PracticeTable.PracticeCity AS Town, 
                        dbo.PracticeTable.PracticeZip AS [Zip Code], dbo.PracticeTable.PracticePhoneNumber AS Phone, dbo.PracticeTable.New_Pat_YN AS [New Pat YN], dbo.PracticeTable.Office_Hours AS [Office Hours], 
                        dbo.PracticeTable.PracticeWeSite AS Web, dbo.practicerecognition.PracRecMonth, dbo.practicerecognition.PracRecYear, dbo.practicerecognition.RecognitionArea, 
                        dbo.practicerecognition.RatingValue
FROM          dbo.PracticeTable RIGHT OUTER JOIN
                        dbo.practicerecognition ON dbo.PracticeTable.OPNumber = dbo.practicerecognition.OPNumber
WHERE      (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.PracticeTable.PrimaryCare = 1) AND (dbo.PracticeTable.AgeCategory = N'Children Only') AND (dbo.practicerecognition.PracRecMonth = 12) OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'OP569') OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'OP143') OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'OP625') OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'OP795') OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'OP385') OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'OP570') OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'OP567') OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'OP567') OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'OP483') OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'OP260') AND (dbo.practicerecognition.PracRecYear = 2015) OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'OP0080') AND 
                        (dbo.practicerecognition.PracRecYear = 2015) OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'OP557') AND (dbo.practicerecognition.PracRecYear = 2015) OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'OP610') AND (dbo.practicerecognition.PracRecYear = 2015) OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'OP616') AND (dbo.practicerecognition.PracRecYear = 2015) OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'OP632') AND (dbo.practicerecognition.PracRecYear = 2015) OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'OP640') AND (dbo.practicerecognition.PracRecYear = 2015) OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'OP452') AND (dbo.practicerecognition.PracRecYear = 2015) OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'OP348') AND (dbo.practicerecognition.PracRecYear = 2015) OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'OP301') AND (dbo.practicerecognition.PracRecYear = 2015) OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'OP443') AND (dbo.practicerecognition.PracRecYear = 2015) OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'OP557') AND (dbo.practicerecognition.PracRecYear = 2015) OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'OP391') AND (dbo.practicerecognition.PracRecYear = 2015) OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'5651') AND (dbo.practicerecognition.PracRecYear = 2015) OR
                        (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.practicerecognition.PracRecMonth = 12) AND (dbo.PracticeTable.OPNumber = N'OP0063') AND (dbo.practicerecognition.PracRecYear = 2015)

[Rabbit]

You said you thought DISTINCT would do "it". But you haven't said what "it" is. Sample data and results would be helpful to understand what you mean by "it".

But here's what DISTINCT does, look at every combination of values in the returned row set, only return one row if there is duplication where every column value is the same in one row compared to another row.

Given this dataset

1     5     7
2     3     8
2     3     9
1     5     7
7     5     1

DISTINCT will return this

1     5     7
2     3     8
2     3     9
7     5     1

[barbarao]

Practice ID	Practice Name	Practice Type	Address 1	Address 2	Town	Zip Code	Phone	New Pat YN	Office Hours	Web
4024	New England State	Primary Care | Adults	7 South Avenue		Portland	4103	(207) xxx-xxxx	Yes	M,W,F:8-4:30, T,Th: 8-6:30

Currently I am getting the location ID demographic data as many times as there are scores.

[barbarao]

The above is what I want to see, not practice ID 4024 6 times. Hope that clarifies.

[Rabbit]

Your sample data only has one row and no score field so I don't know what it is you're trying to show me with that.

But what you described is correct. That's what it should do. The score makes the row distinct because it's different from the other rows.

If that's not what you want, you need to describe more clearly what you're looking for. And again, preferably with sample data and results.

[NeoPa]

BarbaraO:
DISTINCT not working as expected

The simple answer is the most obvious - Change your expectation of what DISTINCT does in line with how it's advertised.

As Rabbit has already said, it only drops rows where all displayed fields have equal values. If you have special fields that you want to treat as those which must be the same then you're probably looking for the GROUP BY clause instead. Remember, with that each field must either be grouped by or aggregated (See Aggregate Query Woes).

[barbarao]

Hi, Here is the SQL. I have two similar ones and this one is less complicated in terms or criteria. I have two tables - one for location and one for ratings. Rating get imported each month and are used for a variety of reports. I need to show the demographic for a location only if it has one or more ratings in the month. I don't need to know which rating it is just that it has rating.

This is what I currently get with the code below:

Location ID LocationName Type Address 1 Address 2 Town Zip Code Phone NewClients Office Hours Web PracRecMonth PracRecYear RecognitionArea
4044 State U Adult xxx South Ave Somewhere xxxx Yes M,W,F:8-4:30, T,Th: 8-6:30 11 2015 2
4044 State U Adult xxx South Ave Somewhere xxxx Yes M,W,F:8-4:30, T,Th: 8-6:30 11 2015 3
4044 State U Adult xxx South Ave Somewhere xxxx Yes M,W,F:8-4:30, T,Th: 8-6:30 11 2015 7
4044 State U Adult xxx South Ave Somewhere xxxx Yes M,W,F:8-4:30, T,Th: 8-6:30 11 2015 17
4044 State U Adult xxx South Ave Somewhere xxxx Yes M,W,F:8-4:30, T,Th: 8-6:30 11 2015 18
4044 State U Adult xxx South Ave Somewhere xxxx Yes M,W,F:8-4:30, T,Th: 8-6:30 11 2015 19
4044 State U Adult xxx South Ave Somewhere xxxx Yes M,W,F:8-4:30, T,Th: 8-6:30 11 2015 20
4044 State U Adult xxx South Ave Somewhere xxxx Yes M,W,F:8-4:30, T,Th: 8-6:30 11 2015 21
What I would like to get is the exact same thing but with the ID only showing one time. Not like it does above. Thanks. Hope this clarifies.

SELECT DISTINCT 
                        dbo.PracticeTable.OPNumber AS [Practice ID], dbo.PracticeTable.PracticeName AS [Practice Name], 'Primary Care | Adults' AS [Practice Type], 
                        dbo.PracticeTable.PracticeAddressLine1 AS [Address 1], dbo.PracticeTable.PracticeAddressLine2 AS [Address 2], dbo.PracticeTable.PracticeCity AS Town, 
                        dbo.PracticeTable.PracticeZip AS [Zip Code], dbo.PracticeTable.PracticePhoneNumber AS Phone, dbo.PracticeTable.New_Pat_YN AS [New Pat YN], dbo.PracticeTable.Office_Hours AS [Office Hours], 
                        dbo.PracticeTable.PracticeWeSite AS Web, dbo.practicerecognition.PracRecMonth, dbo.practicerecognition.PracRecYear, dbo.practicerecognition.RecognitionArea
FROM          dbo.PracticeTable RIGHT OUTER JOIN
                        dbo.practicerecognition ON dbo.PracticeTable.OPNumber = dbo.practicerecognition.OPNumber
WHERE      (dbo.PracticeTable.SiteStatus <> N'Closed') AND (dbo.PracticeTable.PrimaryCare = 1) AND (dbo.PracticeTable.AgeCategory <> N'Children Only') AND (dbo.practicerecognition.PracRecMonth = 11) 
                        AND (dbo.practicerecognition.PracRecYear = 2015)

[barbarao]

Oh, looking at Rabbit's example, I probably can't get what I want based on his DISTINCT 2 and 3 sample. Any ideas on how I can get what I want? Write I now I just run it and then run some VBA on it to deleted duplicate IDs. Thanks again.

[NeoPa]

BarbaraO:
Oh, looking at Rabbit's example, I probably can't get what I want based on his DISTINCT 2 and 3 sample. Any ideas on how I can get what I want?

Perfect question Barbara. You should find an answer to that in post #6.

In short - GROUP BY. But this must be approached with care because of the issues described in the linked article.

[barbarao]

Hi. I found a solution. Built a DISTINCT query on the table that has the Location ID and month, year, etc. Joined it to the table that has the ratings for the Location IDs. Seems to be working. Thanks for helping out. This site is terrific.

[NeoPa]

Glad it worked out for you Barbara.

I'm not sure I understand why you need to DISTINCT the first part if you're JOINing it to another record source anyway, but if it works for you then that's all good.

Thank you for selecting a Best Answer.