37 votes

Stop SQL Server from ordering results?

Let's pretend you've got the white pages of the phone book - remember, that thing Grandpa kept by the fridge so he could call his friends from the war. It's organized by last name, first name. If I ...
Brent Ozar's user avatar
  • 42.9k
32 votes

How to prove the lack of implicit order in a database?

I see three ways to try to convince them: Let them try the same query but with bigger table (more number of rows) or when the table is being updated between executions. Or new rows are inserted and ...
ypercubeᵀᴹ's user avatar
29 votes

How do I order by NULLS LAST if my database doesn't support it?

There is a (closed) connect item requesting support for NULLS LAST in SQL Server. A couple of other ways would be ORDER BY CASE WHEN FullName IS NULL THEN 1 ELSE 0 END, FullName, ...
Martin Smith's user avatar
  • 84.5k
26 votes
Accepted

Only first column in ORDER BY clause is sorted

You are ordering rows of data, not each column separately. The row (10, 1) comes before the row (1, 10) because of your ORDER BY clause. The Value2 only comes into play when there is a tie on the ...
Michael Kutz's user avatar
  • 4,829
22 votes

How to prove the lack of implicit order in a database?

This is the black swan story all over again. If you haven't seen one yet it doesn't mean they don't exist. Hopefully in your case it won't lead to another world wide financial crisis, simply to a few ...
mustaccio's user avatar
  • 25.7k
20 votes
Accepted

Is it REALLY possible that the order will not be guaranteed for this particular redundant derived table?

Most databases are quite clear about the fact that an ORDER BY in a subquery is either: Not allowed: E.g. SQL Server, Sybase SQL Anywhere (unless complemented with TOP or OFFSET .. FETCH) Meaningless:...
Lukas Eder's user avatar
15 votes
Accepted

Are results from UNION ALL clauses always appended in order?

There was a similar question recently in pgsql-docs mailing list, Clarify the ordering guarantees in combining queries (or lack thereof): I was trying to understand what - if any - are the guarantees ...
13 votes
Accepted

ORDER BY gives ERROR: function array_position(text[], character varying) does not exist

Sample data You have a table like, CREATE TABLE research(colors) AS VALUES ('Blue'), ('Orange'), ('Yellow'); ENUM type You have an enumerated list of colors. So the easy thing here would be to ...
Evan Carroll's user avatar
  • 62.7k
13 votes
Accepted

GROUP BY needs ORDER BY NULL to avoid filesort

MySQL 5.7 Reference Manual / ... / SELECT Syntax If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns. To avoid the ...
Akina's user avatar
  • 19.7k
12 votes
Accepted

How to get 0 as array_length() result when there are no elements

should return 0 rather than nothing (NULL), right? It's up for debate what this should return. But the way Postgres defines it, the result for any dimension that does not exist is NULL. COALESCE is ...
Erwin Brandstetter's user avatar
12 votes
Accepted

In PostgreSQL is the row ordering preserved in functions and CTEs?

In SELECT statements the ordering of the returned rows is not guaranteed if the ORDER BY clause is not specified. This is true for all tables, simple or complicated queries. Now, having that as basis, ...
ypercubeᵀᴹ's user avatar
12 votes

Is it REALLY possible that the order will not be guaranteed for this particular redundant derived table?

Yes. Without an ORDER BY clause the output order is undefined and the query planner is well within its purview to assume that you know and understand this. It may decide that because the outer query ...
David Spillett's user avatar
12 votes
Accepted

Adding a ROW_NUMBER() with no column to ORDER BY?

The canonical way to do this is the following: ROW_NUMBER() OVER(ORDER BY (SELECT NULL)). If you're golfing, you might try something like this: SELECT value, n = ROW_NUMBER() OVER(ORDER BY (SELECT 1))...
Joe Obbish's user avatar
  • 32.1k
12 votes

How to prove the lack of implicit order in a database?

Consider the following example, where we have three related tables. Orders, Users, and OrderDetails. OrderDetails is linked with foreign keys to the Orders table and the Users Table. This is ...
Hannah Vernon's user avatar
  • 70k
12 votes
Accepted

Are the results of the STRING_SPLIT() function returned in a deterministic order?

No, they are not returned in a deterministic order. While you are unlikely to see them returned in a different order, that doesn't make the current behavior deterministic or reliable. Tricks like ...
Aaron Bertrand's user avatar
10 votes

How to prove the lack of implicit order in a database?

As a practical example, in Postgres, the order currently changes when you update a row: % SELECT * FROM mytable; id | data ----+------ 0 | a 1 | b 2 | c 3 | d 4 | e 5 | f 6 | g 7 | ...
JoL's user avatar
  • 279
10 votes
Accepted

SELECT DISTINCT ON, ordered by another column

You can still use DISTINCT ON. Just wrap it into an outer query to sort to your needs. See: Get distinct on one column, order by another PostgreSQL DISTINCT ON with different ORDER BY SELECT * FROM ...
Erwin Brandstetter's user avatar
9 votes

Order by column should have index or not?

For anyone wondering about the answer as it relates to PostgreSQL, the answer is yes an index will be used. As per the PostgreSQL documentation the query planner "will consider satisfying an ORDER BY ...
douglasr's user avatar
  • 199
9 votes

SQL Server: Order of Rows

This article by Conor Cunningham (Software Architect, SQL Server Engine at Microsoft) should answer your questions: No Seatbelt – Expecting Order without ORDER BY Some extracts below: The hard part ...
Scott Hodgin - Retired's user avatar
9 votes

SQL Server: Order of Rows

under what circumstances does SQL SERVER order the result even if unasked? This might happen if the access path to retrieve the data happens to produce data already ordered e.g. an index scan in ...
Martin Smith's user avatar
  • 84.5k
9 votes
Accepted

What is the order when SELECT-ing unordered set from an ordered SELECT?

Can I trust that the inner order is respected by the outer projection as well ...? No, you cannot. ... or do I need to state the ORDER BY in the outer statement explicitly, to be sure? Yes, you ...
ypercubeᵀᴹ's user avatar
9 votes
Accepted

Sqlite: Finding the next or previous element in a table consisting of integer tuples

At the moment I try to find the next tuple with ("center" is (1-1-1-3)) Test SELECT * FROM tuples WHERE (1,1,1,3) < (a,b,c,d) ORDER BY a,b,c,d LIMIT 1 fiddle
Akina's user avatar
  • 19.7k
8 votes
Accepted

Does an order by clause have a negative effect on performance?

An ORDER BY clause will always add a sort operation if there is no usable index to retrieve the data in order. For larger sets of data the data being sorted may be written to disk adding additional ...
BillThor's user avatar
  • 4,468
8 votes
Accepted

How do I order by NULLS LAST if my database doesn't support it?

Select FullName, Login FROM [User] ORDER BY CASE WHEN FullName IS NULL THEN 'ZZZZZZZ' ELSE FullName END, login asc
Kenneth Fisher's user avatar
8 votes

Customize jsonb key sort order involving arrays

First off, your question as well as your column name "key" are misleading. The column key does not contain any JSON keys, only values. Else we could use the function jsonb_object_keys(jsonb) to ...
Erwin Brandstetter's user avatar
8 votes
Accepted

How to force order of WHERE clauses in PostgreSQL?

This information is crucial: the first row matched is the winner (limit 1) You do not actually need any sort order for a single result. ORDER BY is just one idea how to solve the task. I suggest a ...
Erwin Brandstetter's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible