Geek asks and answers

Does ORDER BY apply before or after DISTINCT?

In a MySQL query when using a parameter, DISTINCTis it applied ORDER BYafter removing duplicates? If not, is there a way to do this? I think this causes some problems with my code.

EDIT :
Here is some more information about what causes my problem. I understand that at first glance this order would not be important since I am dealing with duplicate lines. However, this is not entirely true, as I use INNER JOINto sort the strings.

Let's say I have a forum thread table containing this data:

+----+--------+-------------+
| id | userid |    title    |
+----+--------+-------------+
|  1 |      1 | Information |
|  2 |      1 | FAQ         |
|  3 |      2 | Support     |
+----+--------+-------------+

I also have a set of messages in another table, for example:

+----+----------+--------+---------+
| id | threadid | userid | content |
+----+----------+--------+---------+
|  1 |        1 |      1 | Lorem   |
|  2 |        1 |      2 | Ipsum   |
|  3 |        2 |      2 | Test    |
|  4 |        3 |      1 | Foo     |
|  5 |        2 |      3 | Bar     |
|  6 |        3 |      5 | Bob     |
|  7 |        1 |      2 | Joe     |
+----+----------+--------+---------+

MySQL , (, :

SELECT t.*
FROM Threads t
INNER JOIN Posts p ON t.id = p.threadid
ORDER BY p.id DESC

- :

+----+--------+-------------+
| id | userid |    title    |
+----+--------+-------------+
|  1 |      1 | Information |
|  3 |      2 | Support     |
|  2 |      1 | FAQ         |
|  3 |      2 | Support     |
|  2 |      1 | FAQ         |
|  1 |      1 | Information |
|  1 |      1 | Information |
+----+--------+-------------+

, , , . , SELECT DISTINCT. :

+----+--------+-------------+
| id | userid |    title    |
+----+--------+-------------+
|  3 |      2 | Support     |
|  2 |      1 | FAQ         |
|  1 |      1 | Information |
+----+--------+-------------+

, , , "" . , DISTINCT , . .

, ?

+5
3

:

  • , , ORDER BY; , (.. ORDER BY ), , , , undefined.

    , , : , , ORDER BY , , , .

  • DISTINCT GROUP BY, ; SELECT DISTINCT a, b, c FROM t , ORDER BY a, b, c. , .

, № 2 , , DISTINCT p.id; :

SELECT   t.*
FROM     Threads t INNER JOIN Posts p ON t.id = p.threadid
GROUP BY t.id
ORDER BY MAX(p.id) DESC
+5

DISTINCT MySQL , , ORDER BY , . , : DISTINCT -, ORDER BY .

+1

, DISTINCT ORDER BY, .

However, if you also use GROUP BY, this will affect the final output. In this case, it is ORDER BYexecuted after GROUP BY, which returns unexpected results (if you expect the sorting to be done before grouping).

+1
source

More articles:


All Articles