-1

An simple MemSQL listing show results on a sorted sequence to each time the query is loaded.

Query on different results per run: select * from VENDAS_POR_DIA; Table on real sequence (ORDER):

MySQL [bucket_8]> select * from VENDAS_POR_DIA order by 1;
+------------+------+
| data_envio | qtde |
+------------+------+
| 2017-09-04 |   63 |
| 2017-09-05 |   48 |
| 2017-09-06 |  813 |
| 2017-09-07 |  177 |
| 2017-09-08 |  679 |
| 2017-09-09 | 1034 |
| 2017-09-10 |   76 |
| 2017-09-11 |  332 |
| 2017-09-12 |  542 |
| 2017-09-13 |  414 |
| 2017-09-14 |  366 |
| 2017-09-15 |  517 |
| 2017-09-16 |  197 |
| 2017-09-17 |   23 |
| 2017-09-18 |  346 |
| 2017-09-19 |  520 |
| 2017-09-20 |  149 |
| 2017-09-21 |  288 |
+------------+------+
18 rows in set (0.20 sec)

But when I dont use ORDER BY, the sequence of the order changes for each new run. How to prevent this behavior.

calebeaires
  • 1,972
  • 1
  • 22
  • 34
  • 1
    SQL tables and result sets represent *unordered* sets. If you want results in a particular order, use `order by`. – Gordon Linoff Sep 22 '17 at 11:32
  • I did the same query on MySQL and if I run the same command with the same data, the results is aways on the order the rows were inserted. – calebeaires Sep 22 '17 at 11:35
  • 1
    You should never depend on *observed* behavior being consistent behavior, unless it is backed up by documentation that it will always work. – Gordon Linoff Sep 22 '17 at 11:41

1 Answers1

2

If you don't use ORDER BY, you may get results in any order - the ordering is unspecified and different databases may give certain orders or not depending on their implementation. MySQL happens to give you results in a certain order in some situations, but this is not a semantic guarantee. If you want ordering, you must specify ORDER BY.

See https://dev.mysql.com/doc/refman/5.7/en/sorting-rows.html ("result rows are displayed in no particular order") and https://docs.memsql.com/concepts/v5.8/mysql-features-unsupported-in-memsql/#behavior-differences.

You can see plenty more answers about MySQL's behavior, for example here: SQL: What is the default Order By of queries?.

Jack Chen
  • 1,216
  • 1
  • 7
  • 11