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
....
|
Hi Cuero ,
Here I am using PostgreSql and this code is working in it …
If you query for
creating table is like this :
CREATE TABLE public.table
(
id text,
name text
);
Then for Obtaining above result You can use this code :
SELECT Distinct CAST(coalesce(id) AS Integer) FROM public.table ORDER BY CAST(coalesce(id) AS Integer);
Note :
coalesce : Evaluates the arguments in order and returns the
current value of the first expression that initially does not evaluate to NULL.
Comments
Post a Comment