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?
