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 ...
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 ...
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....
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 ...
To produce your desired output, you can simply:
SELECT id, version
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 ...
There is a (closed) connect item requesting support for NULLS LAST in SQL Server.
A couple of other ways would be
CASE WHEN FullName IS NULL THEN 1 ELSE 0 END,
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 ...
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 ...
OK, enough brain cells are dead.
WITH cte AS
CAST(0 AS varbinary(max)) AS Level
WHERE [ParentID] = 0
Level + CAST(i.[ICFilterID] AS ...
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 (...
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 ...
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 ...
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. ...
If I want to move record 0 to the start, I have to reorder every record
No, there's a simpler way.
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 ...
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 ...
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, *
WHERE parent IS NULL
SELECT r.depth + 1, r.path || n.node, n.*
FROM node_rec r
JOIN nodes n ON n.parent = ...
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 ...
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),
(3, 1)) V(A, B)
ORDER BY B
Returns (ordered by alias)
| B ...
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 ...
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 ...
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.
IF OBJECT_ID(N'dbo.OrderDetails', N'U') IS ...
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 ...
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".
For your request:
sorted by alphabets and then by numeric values
I assume (deriving from your sample data) you want to ORDER BY:
The first letter, treated as text.
The first number (consecutive digits), treated as integer.
The whole string to break remaining ties, treated as text. May or may not be needed.
ORDER BY left(col, 1) -- ...
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 ...
My question is: why does this not use the index amplifier_saturation_start?
Even with 30,000,000 rows, only 3,500 in the date range it can be faster to read tuples from the top of the index amplifier_saturation_lddate on lddate. The first row that passes the filter on start can be returned as is. No sort step needed. With a perfectly random ...
First create an auxiliary numbers table larger than the maximum range you will ever be interested in.
CREATE TABLE dbo.Numbers
N INT primary key
WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
E02(N) AS (SELECT 1 FROM E00 a, E00 b),
E04(N) AS (SELECT 1 FROM E02 a, E02 b),
E08(N) AS (SELECT 1 FROM E04 a, E04 b),
E16(N) AS (SELECT 1 ...
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, we should consider that Postgres has implemented CTEs in a peculiar way. They are always materialized (see: PostgreSQL’s CTEs are optimisation fences).
You have a table like,
CREATE TABLE research(colors)
AS VALUES ('Blue'), ('Orange'), ('Yellow');
You have an enumerated list of colors. So the easy thing here would be to use an ENUM type
CREATE TYPE colors AS ENUM ('Red','Orange','Yellow','Green','Blue');
ALTER TABLE research
ALTER COLUMN colors -- myColorsColumn