How does SELECT DISTINCT work in MySQL?

Question

I have a table with multiple rows which have a same data. I used SELECT DISTINCT to get a unique row and it works fine. But when i use ORDER BY with SELECT DISTINCT it gives me unsorted data.

Can anyone tell me how distinct works?

Based on what criteria it selects the row?

Answer 1

From your comment earlier, the query you are trying to run is

Select distinct id from table where id2 =12312 order by time desc.

As I expected, here is your problem. Your select column and order by column are different. Your output rows are ordered by time, but that order doesn't necessarily need to preserved in the id column. Here is an example.

id  | id2    | time
-------------------
1   | 12312  | 34
2   | 12312  | 12
3   | 12312  | 48

If you run

SELECT * FROM table WHERE id2=12312 ORDER BY time DESC

you will get the following result

id  | id2    | time
-------------------
2   | 12312  | 12
1   | 12312  | 34
3   | 12312  | 48

Now if you select only the id column from this, you will get

id
--
2
1
3

This is why your results are not sorted.

Answer 2

When you specify SELECT DISTINCT it will give you all the rows, eliminating duplicates from the result set. By "duplicates" I mean rows where all fields have the same values. For example, say you have a table that looks like:

 id  |  num
 --------------
 1   |  1 
 2   |  3
 3   |  3

SELECT DISTINCT * would return all rows above, whereas SELECT DISTINCT num would return two rows:

num
-----
1
3

Note that which row actual row (eg: whether it's row 2 or row 3) it selects is irrelevant, as the result would be indistinguishable.

Finally, DISTINCT should not affect how ORDER BY works.

Reference: MySQL SELECT statement