CODEWITHSUNDEEP
sqlmysqlpostgresql
Devin Dixon
Devin Dixon

Reputation: 12403

Using Distinct with *

I am having a problem using distinct with *. I have a table that as a join and I am trying to do a statement like:

SELECT DISTINCT Name, * FROM table_a JOIN table_a.id=table_b.id WHERE status=1

But it is not allowing me to do so. Is there a way of using Distinct with * option?

Upvotes: 1

Views: 293

Answers (4)

Dyin
Dyin

Reputation: 5376

Basically the solution is more like this:

SELECT *
FROM table_a, table_b
JOIN table_a.id = table_b.id
WHERE status = 1
GROUP BY table_a.name

This is useful, if you want to avoid the enumeration of attributes. When you do, and the relation changes (one attribute is removed or added), your query will not work.

Read this: MySQL 8.3.1.13. DISTINCT Optimization

Upvotes: 0

fredt
fredt

Reputation: 24372

It is not clear what you want to do.

Maybe you want to return only one column with a given name (not two id columns).

In this case the standard SQL query is like this:

SELECT * FROM table_a JOIN table_b USING (id) WHERE status=1

The keyword DISTINCT means that after performing the select, duplicate rows are eliminated (not duplicate column names).

Upvotes: 0

Ante
Ante

Reputation: 5458

DISTINCT * probably will return all rows, because they are distinct:-)

To get all different names use DISTINCT name or to retrieve some statistics about names (e.g. count) use SELECT name, COUNT(*) FROM ... GROUP BY name.

Upvotes: 1

tvanfosson
tvanfosson

Reputation: 532515

I don't have access to test this right now, but I suspect that the problem is not with distinct but rather that you have columns in each table with the same names (like id) and it doesn't know which of the two conflicting columns to choose. Does it change if you do select distinct table_a.*, table_b.* ...?

Upvotes: 1

Related Questions