CODEWITHSUNDEEP
mysqlgroup-bydistinct
Luis Alvarado
Luis Alvarado

Reputation: 9406

Why does DISTINCT show different results when using GROUP BY in MYSQL

I have the following 2 queries:

SELECT id, sex FROM user WHERE sex=0 GROUP BY id LIMIT 10; With the following output:

+----+-----+
| id | sex |
+----+-----+
|  3 |   0 |
|  6 |   0 |
| 30 |   0 |
| 36 |   0 |
| 38 |   0 |
| 40 |   0 |
| 43 |   0 |
| 46 |   0 |
| 48 |   0 |
| 54 |   0 |
+----+-----+
10 rows in set (0.04 sec)

And

SELECT DISTINCT id, sex FROM user WHERE sex=0 LIMIT 10;

+------+-----+
| id   | sex |
+------+-----+
|  721 |   0 |
|  440 |   0 |
|  485 |   0 |
| 2388 |   0 |
| 1215 |   0 |
| 1218 |   0 |
| 6569 |   0 |
| 5123 |   0 |
| 5178 |   0 |
| 5177 |   0 |
+------+-----+
10 rows in set (0.03 sec)

But they show different results. What makes them create different results. I was under the impression that LIMIT was applied after everything was done. Could LIMIT be affecting this, or do DISTINCT and GROUP BY behave differently when trying to find unique/distinct values. Do they order them before outputting the result?

Upvotes: 1

Views: 670

Answers (2)

RomanKonz
RomanKonz

Reputation: 1027

beause your query is not deterministic. if you don't add an order by to your query, mysql chooses the data that is accessed the fastest way. if you change your query, mysql can change his execution plan, so other rows will be determined for fastest access.

if you want a deterministic (every time it should return the same) result, add an order by, that orders all business key (or primary key) columns in the proper order

this will be deterministic:

SELECT DISTINCT id, sex 
FROM user 
WHERE sex=0 
order by id
LIMIT 10

edit: it's because GROUP BY implies an 'order by', distinct not, because group by has to "group" in a temporary table, so it can aggregate, meanwhile distinct ignores duplicate values

Upvotes: 1

amphibient
amphibient

Reputation: 31358

because you are not using an ORDER BY clause. if you use the ORDER BY clausweree, it would sort the resultset, which should return the same result in both cases

Upvotes: 1

Related Questions