Bob Cullen-434885 (1/23/2015)
Hmm. Not sure about this. I tried select distinct * from mytable, and it returned all the rows, which was correct. So you can use * with distinct, just not if you wrap count() round it.
Why, oh, why, does SQL behave like this?
Because the * in SELECT * has a different meaning from the * in COUNT(*), and the DISTINCT in SELECT DISTINCT has a differernt meaning from the DISTINCT in COUNT(DISTINT ...).
In SELECT *, the * is shorthand for "all columns". This results in a resultset containing all columns. SELECT DISTINCT ... means that rows that have the same value in all columns are returned only once. So * is shorthand for a set of columns, and DISTINCT operates on a set of columns. Works,.
In COUNT(*), the * is shorthand for "rows". COUNT(*) counts rows, regardless of values. In COUNT(DISTINCT ...), the DISTINCT means that duplicate values are counted only once.. So * in COUNT(*) represents a full row, and DISTINCT in COUNT(DISTINCT ...) operates on single (scalar) values. A row is not a scalar value, so this does not work.