68

For the record SELECT * FROM mytable WHERE id IN (1,2,3,4) ORDER BY FIELD(id,3,2,1,4); should work as well because you do not have to order the list in the WHERE clause As for how it works, FIELD() is a function that returns the index position of a comma-delimited list if the value you are searching for exists. IF id = 1, then FIELD(id,3,2,1,4) returns ...


47

The appearance of an ordered result set, without an ORDER BY clause, often results from a scan retrieving rows in index order. One reason why an index-order scan is generally chosen under the default READ COMMITTED isolation level is that it reduces the chances of unwanted concurrency anomalies such as encountering the same row multiple times, or entirely ...


39

(Indexed views aside, of course.) A view is not materialized - the data isn't stored, so how could it be sorted? A view is kind of like a stored procedure that just contains a SELECT with no parameters... it doesn't hold data, it just holds the definition of the query. Since different references to the view could need data sorted in different ways, the way ...


36

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 asked you to get that phone book and read out the names: SELECT FirstName, LastName FROM dbo.PhoneBook You would usually read them out to me in last name order....


30

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 some old ones are deleted. Or an index is added or removed between executions. Or the table is vacuumed (in Postgres). Or indexes are rebuilt (in SQL Server). Or ...


28

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, Login I prefer this as it doesn't rely on hardcoding a string that it is assumed no legitimate data will sort after. I'd rather not have to consider ...


25

OK, enough brain cells are dead. SQL Fiddle WITH cte AS ( SELECT [ICFilterID], [ParentID], [FilterDesc], [Active], CAST(0 AS varbinary(max)) AS Level FROM [dbo].[ICFilters] WHERE [ParentID] = 0 UNION ALL SELECT i.[ICFilterID], i.[ParentID], i.[FilterDesc], i.[Active], Level + CAST(i.[ICFilterID] AS ...


24

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 first column. As further explained by Hellion, as far as the database is concerned, the pair (10, 1) is an indivisible unit: it's not two values, it's one set (...


22

Absolutely not. Proof: SELECT A.[Name], ROW_NUMBER() OVER(ORDER BY A.[Name] ASC), ROW_NUMBER() OVER(ORDER BY A.[Name] DESC) FROM [FooTable] AS A The only way to guarantee an order in SQL is to ask for it, use ORDER BY on the result itself.


22

To produce your desired output, you can simply: SELECT id, version FROM versions ORDER BY string_to_array(version, '.')::int[]; One can cast a whole text array to an integer array (to sort 9 before 10). One can ORDER BY array types. This is the same as ordering by each of the elements. And shorter arrays come before longer ones with identical leading ...


20

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: E.g. PostgreSQL, DB2 (again, unless complemented with OFFSET .. FETCH or LIMIT) Here's an example from the DB2 LUW manual (emphasis mine) An ORDER BY ...


19

The answer for this simple case is Yes. Rows are inserted in the provided order in the VALUES expression. And if your id column is a serial type, values from the underlying sequence will be fetched in that order. But this is an implementation detail and there are no guarantees. In particular, the order is not necessarily maintained in more complex queries ...


19

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 unhappy customers. Postgres documentation says this explicitly: If ORDER BY is not given, the rows are returned in whatever order the system finds fastest ...


18

No, your colleague is wrong. All SQL proroducts - DBMS that behave according to the SQL standards - provide no guarantee that the result of a query output will be ordered in any way, unless there is an ORDER BY clause in the query. As the IBM DB2 docs mention: Ordering is performed in accordance with the comparison rules described in Language elements. ...


17

Since you haven't told SQL Server how to order the results, it is free to do so in whatever is the most efficient. In this way it will depend on what is the cheapest to sort, and the columns you select will drive that because it in turn depends on the cheapest index(es) to use to get at the information requested by the query. This can change from execution ...


17

If I want to move record 0 to the start, I have to reorder every record No, there's a simpler way. update your_table set order = -1 where id = 0; If I want to insert a new record in the middle, I have to reorder every record after it That's true, unless you use a data type that supports "between" values. Float and numeric types allow you to update a ...


14

If you had asked the question I think you actually meant to ask: How can I order by ROW_NUMBER() without repeating the complex ORDER BY expression? We could have told you to create an alias for the ROW_NUMBER() expression, and then sort using the alias: SELECT A.[Name], rn = ROW_NUMBER() OVER (ORDER BY <complex expression>) FROM dbo.[...


14

Is it indeed necessary for all selected columns to be indexed in order for MySQL to choose to use the index? This is a loaded question because there are factors that determine whether an index is worth using. FACTOR #1 For any given index, what is the key population? In other words, what is the cardinality (distinct count) of all tuples recorded in the ...


14

You're going to have to make your application not put the ORDER BY inside the subquery (maybe it has an option to not use a needless subquery in the first place). As you've already discovered, this syntax is not supported in SQL Server without TOP. And with TOP, unless you want to leave some rows out, using TOP 100 PERCENT is going to render the ORDER BY ...


13

If you are running on compatibility level of 80 (SQL-Server 2000), then this is the expected behaviour. It was corrected in version 2005. You can check the Compatibility levels page at MSDN where the differences are listed. In the section "Differences Between Lower Compatibility Levels and Level 90", one of the many items is: When binding the column ...


12

If an alias is used in an ORDER BY it must be used on its own, not inside an expression. If inside any kind of expression it tries to resolve it to a column in the base table sources not as an alias. So for example SELECT A AS B FROM (VALUES (1, 3), (2, 2), (3, 1)) V(A, B) ORDER BY B Returns (ordered by alias) +---+ | B ...


12

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 doesn't specify an order it can drop the ordering in the inner query to avoid a sort operation, especially if there is no clustered index or no index at all to ...


12

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 essentially a very typical setup for relational databases; arguably the entire purpose of a relational DBMS. USE tempdb; IF OBJECT_ID(N'dbo.OrderDetails', N'U') IS ...


11

An array representing the path from the root up to the leaf should achieve the desired sort order: WITH RECURSIVE node_rec AS ( (SELECT 1 AS depth, ARRAY[node] AS path, * FROM nodes WHERE parent IS NULL LIMIT 10 ) UNION ALL SELECT r.depth + 1, r.path || n.node, n.* FROM node_rec r JOIN nodes n ON n.parent = ...


11

As was pointed out in ypercube's answer, when there is no ORDER BY clause there is no defined order. What I would like to add is that it's important to realise that SQL is very much an abstraction, it does not specify step by step what the DBMS is to do but rather specifies your requirements of the end result. This implies that if the data is already ...


11

When I simply dump all records from the table ...then you should not expect any order. In fact the same query run multiple times could come back in a different order without warning. The reason is that your two queries - which are "different" queries most likely because of the different query text, not because of the hint - have different execution plans (...


11

James nicely explained how this works, but I'd just like to reiterate one thing: unless you use an ordering function, the order of rows in the result set is undefined. If you need a given ordering, use an explicit order by clause - if you don't specify it, you're basically saying "I don't care about the order at all", not "Order it by the clustered index".


10

The query you posted is not valid for creating a view; running CREATE VIEW xy AS for this query will result in an error. Are you using a TOP clause? A view, being a table expression (a set), can't have the order defined, since that would be against the principles of a relational model (there is no order for rows in a relational table - a set is an unordered ...


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