| Subject | Select Distinct returning multiple rows |
|---|---|
| Author | Dalton Calford |
| Post date | 2017-12-11T19:37:39Z |
I have a weird problem.
An over-simplified description of the problem is as follows
select a.FOO, a.BAR from FOOBAR a
returns two rows.
| a.FOO | a.BAR |
| A | 124 |
| A | 124 |
select distinct a.FOO, a.BAR from FOOBAR a
returns the same two rows
| a.FOO | a.BAR |
| A | 124 |
| A | 124 |
BUT
select a.FOO, a.BAR, count(*) from FOOBAR a group by a.FOO, a.BAR
returns one row
| a.FOO | a.BAR | count |
| A | 124 | 2 |
So, the group by recognises that the two rows are identical, while distinct does not.
The above is an oversimplification of the query (which joins many tables etc)
So, I tried to isolate the distinct clause from the underlying tables using a cte
ie,
with MYFOOBAR as (
select a.FOO, a.BAR, count(*) from FOOBAR a group by a.FOO, a.BAR)
select distinct b.FOO, b.BAR from MYFOOBAR b
but, that still returns two rows.
The underlying tables do not have nulls.
What I am trying to understand is what makes the distinct directive
consider the rows differently from a group by directive.
From a logical standpoint, both should resolve things the same
should they not (one to eliminate duplicates from the result set, the other to count duplicates)
What am I missing here?