CODEWITHSUNDEEP
mysqlsqldistinct
Cuero
Cuero

Reputation: 1209

Will DISTINCT always return a same order of values for a same SQL?

I have a sql statement is with pattern

SELECT id FROM table WHERE [conditions] ORDER BY [orders] LIMIT 10000 OFFSET 0

and the return value is like below:

id
-----
1
0
0
0
0
1
1
1
2
2
...

Then I want to get the distinct value as the order of their first appearance, since there's an ORDER BY in the sql, and DISTINCT or GROUP BY are both happened before ORDER BY in a sql, I tried below sql.

SELECT DISTINCT id FROM (SELECT id FROM table WHERE [conditions] ORDER BY [orders] LIMIT 10000 OFFSET 0) tmp;

And the result is like what I want:

id
----
1
0
2
...

My question is: can I ensure that in a same pattern SQL, DISTINCT will always return the distinct id as the order their first appearance?

Thanks.

---------------Notes------------------

Below can be ignored. I just noticed many peoples are recommended to try GROUP BY, so I tried below sql as well:

SELECT id FROM (SELECT id FROM table WHERE [conditions] ORDER BY [orders] LIMIT 10000 OFFSET 0) tmp GROUP BY id;

but the returning is reordered by the alpha-beta order (it's not integer order because the column is a CHAR column for the real id is a string), which is not what I want.

id
----
0
1
10
100
....

Upvotes: 1

Views: 844

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269963

If you want results in a particular order, then you need to specify that in the ORDER BY for the outermost SELECT. That suggests something like this:

SELECT id
FROM tabl
WHERE [conditions]
ORDER BY [orders]
LIMIT 10000 OFFSET 0

Then, if you want to order by the first appearance, you need a column that specifies the first appearance. Let's call this CreatedAt (perhaps it is orders?). If so:

SELECT id
FROM table
WHERE conditions
GROUP BY id
ORDER BY min(CreatedAt)
LIMIT 10000;

Note: SQL tables represent unordered sets, so you need a column to specify the ordering of interest.

Upvotes: 2

Related Questions