Can we use "*" with distinct ? If yes how ? If not why ?

Question

Query about DISTINCT

Answer 1

This should work for the most part. It might depend on your database.

For instance, MS SQL Server will throw an error if any column is a TEXT datatype. The reason is because text datatypes are not comparable in the way varchars and numbers are.

This doesn't seem to be a problem in MySQL.

I'm assuming you're asking because you ran into a problem. If so, what errors are you receiving?

Answer 2

SELECT DISTINCT * FROM MyTable

This can be used if MyTable has duplicate records and you wish to eliminate the duplicates. You can select INTO another table if you wish to purge the duplicates. Afterward, DROP the original table and rename the new one to the original name.

There are more uses for SELECT DISTINCT *, such as eliminating duplicates that can occur from table or view JOINs, or SELECTs from multiple tables.

Answer 3

The most common use of * is to find the total number of records in a table. For eg-

SELECT COUNT(*) FROM table_name;

I personally have not come across a query which makes use of both * and distinct together. Let us see what others have to say.