SELECT DISTINCT. Please explain?

Question

Wondering if someone could please explain the difference between these two queries and advise why one works and the other doesn't.

This one works. Gives me two records of the distinct GantryRtn value and their corresponding SSD value.

SELECT DISTINCT GantryRtn as Gantry, ROUND(Field.SSD,1) as SSD
            FROM Field, PlanSetup, Course, Patient, Radiation
                    WHERE Field.RadiationSer=Radiation.RadiationSer
                        AND Radiation.PlanSetupSer=PlanSetup.PlanSetupSer
                        AND PlanSetup.CourseSer=Course.CourseSer
                        AND Course.PatientSer=Patient.PatientSer
                        AND Patient.PatientId='ZZZ456'
                        AND PlanSetup.PlanSetupId='F T1 R CHEST'

However there is a foreign key in the Field table that links to the primary key of another table that contains a plain text name for each field. I'd also like to extract that name (in a separate query if I have to) by pulling out this foreign key RadiationSer. But as soon as I put RadiationSer into the query, I lose my DISTINCT result.

SELECT DISTINCT GantryRtn as Gantry, ROUND(Field.SSD,1) as SSD, Field.RadiationSer
            FROM Field, PlanSetup, Course, Patient, Radiation
                    WHERE Field.RadiationSer=Radiation.RadiationSer
                        AND Radiation.PlanSetupSer=PlanSetup.PlanSetupSer
                        AND PlanSetup.CourseSer=Course.CourseSer
                        AND Course.PatientSer=Patient.PatientSer
                        AND Patient.PatientId='ZZZ456'
                        AND PlanSetup.PlanSetupId='F T1 R CHEST'

This second query gives me 7 records with non-distinct GantryRtn values.

Why does this happen??

I have investigated using GROUP BY but this slows the query down and appears to pull ALL GantryRtn's out of the database (100s of records).

Thanks Greg

possible duplicate of [Select distinct from multiple fields using sql](http://stackoverflow.com/q/546804/), [sql query distinct on multiple columns](http://stackoverflow.com/q/1503080/), [DISTINCT on multiple columns](http://stackoverflow.com/q/5804529/) — outis, Jun 29 '12 at 01:19
Hi outis, Thanks but I don't understand why those queries (and my first one) work but my second one doesn't. I am already selecting multiple columns with one DISTINCT in my first query. I want to understand WHY adding another column causes the DISTINCT to fail? So the question is "Why doesn't it work?" rather than "How do I make this work?" — Barbs, Jun 29 '12 at 01:46

score 4 · Accepted Answer · answered Jun 29 '12 at 01:14

4

The DISTINCT keyword applys to a result set (all fields) and not just to the first field.

In your case:

SELECT DISTINCT GantryRtn as Gantry, ROUND(Field.SSD,1) as SSD, Field.RadiationSer

will return any records that are distinct (not the same) when taken together with Gantry, SSD, and RadiationSer

So, you may have 7 records for the same Gantry and with different values for RadiationSer.

If you'd like to first filter by distinct Gantry values you can accomplish that with a sub-query and an inner join but somehow you must settle on which RadiationSer value to use.

answered Jun 29 '12 at 01:14

Mike Dinescu

54,171
16
118
151

Thanks for your response Miky. Ok so I think I now understand how I am misinterpreting the DISTINCT keyword. The _RadiationSer_ is a unique value assigned to each _Field_ so if there are 7 fields there are 7 RadiationSers. I want to isolate those RadiationSer that have distinct _GantryRtn_ values and report the SSD for those unique fields. I will spend some time on this and post in a separate question if I struggle to construct the code for it. – Barbs Jun 29 '12 at 02:08
Solved with about 5 lines of PHP. Thanks for your help. :) – Barbs Jun 29 '12 at 02:15

SELECT DISTINCT. Please explain?

1 Answers1