SQL best practice to deal with default sort order

sql database sql-order-by
Yada picture Yada · Nov 24, 2009 · Viewed 39k times · Source

A lot of SQL code I've read, it seems like the developer assumes that the default sort order always holds. For example when building an HTML select list they would just SELECT id, name FROM table without issuing an ORDER BY clause.

From my own experience it seems like dbms alway orders data using FIFO if no ORDER BY clause is given and no index. However, the order is not guaranteed. But I have never seen a dbms reordering data if there no change to the table.

Have you ever experienced a dbms selecting data in a non deterministic order if there is no change to the table?

Is it best practice to always put an ORDER BY clause?

Answer

HLGEM picture HLGEM · Nov 24, 2009

There is no default sort order. Even if the table has a clustered index, you are not guaranteed to get the results in that order. You must use an order by clause if you want a specific order.

Related questions

Insert into ... values ( SELECT ... FROM ... )

I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the SQL engine of the day (…

Read More
What is the difference between "INNER JOIN" and "OUTER JOIN"?

Also how do LEFT JOIN, RIGHT JOIN and FULL JOIN fit in?

Read More
'IF' in 'SELECT' statement - choose output value based on column values

SELECT id, amount FROM report I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?

Read More