Count of Distinct Rows Without Using Subquery

Question

Say I have Table1 which has duplicate rows (forget the fact that it has no primary key...) Is it possible to rewrite the following without using a JOIN, subquery or CTE and also without having to spell out the columns in something like a GROUP BY?

SELECT COUNT(*)
FROM (
    SELECT DISTINCT * FROM Table1
) T1

Answer 1

if you really really want to do that you can just "SELECT COUNT(*) FROM table1 GROUP BY all,columns,here" and take the size of the result set as your count.

But it would be dailywtf worthy code ;)

Answer 2

You can do something like this.

SELECT Count(DISTINCT ProductName) FROM Products

but if you want a count of completely distinct records then you will have to use one of the other options you mentioned.

If you wanted to do something like you suggested in the question, then that would imply you have duplicate records in your table.

If you didn't have duplicate records SELECT DISTINCT * from table would be the same without the distinct.

Answer 3

No, it's not possible.

If you are limited by your framework/query tool/whatever, can't use a subquery, and can't spell out each column name in the GROUP BY, you are SOL.

If you are not limited by your framework/query tool/whatever, there's no reason not to use a subquery.