Skip to main content
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
33 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
  • 85.4k
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
  • 26.3k
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
16 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
  • 63.8k
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
  • 20.1k
13 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.5k
13 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
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

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
  • 70.2k
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
11 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
  • 289
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
  • 85.4k
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
  • 20.1k
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

Filter on array text[] and sort on timestamp

General considerations Index optimization always depends on the complete picture. Table size, row size, cardinalities, value frequencies, selectivity of typical queries, Postgres version, typical ...
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
8 votes

Stop SQL Server from ordering results?

MS SQL has the inclination to order results even if I don’t want it to. By default, data appears to be ordered in primary key order, even if the primary key is nonclustered. Now it seems ...
sepupic's user avatar
  • 11k
8 votes
Accepted

How to combine ORDER BY and LIMIT with an aggregate function?

I am applying order by p.name desc in two places ... Is there a way to avoid that? Yes. Aggregate with an ARRAY constructor in the lateral subquery directly: SELECT c.identifier, p.persons FROM ...
Erwin Brandstetter's user avatar
8 votes
Accepted

Adding an additional "order by" column gives me a much worse plan

Your question is missing a lot of detail but I can reproduce something similar. Setup CREATE TABLE T1(X INT PRIMARY KEY, Y INT INDEX IX) CREATE TABLE T2(X INT, Y INT , PRIMARY KEY(X, Y)) INSERT ...
Martin Smith's user avatar
  • 85.4k

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