Hot answers tagged

42

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 ...


25

(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 ...


22

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 ...


20

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.


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

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 ...


16

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 ...


15

Since all your matches have to match the LIKE pattern in order to be included, the simple thing to do is assume that shorter values for the column you're matching are "better" matches, as they're closer to the exact value of the pattern. ORDER BY LEN(item_nale) ASC Alternatively, you could assume that values in which the match pattern appear earlier are "...


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

Yes, MySQL can use an index on the columns in the ORDER BY (under certain conditions). However, MySQL cannot use an index for mixed ASC,DESC order by (SELECT * FROM foo ORDER BY bar ASC, pants DESC). Sharing your query and CREATE TABLE statement would help us answer your question more specifically. For hints on how to optimize ORDER BY clauses: http://dev....


12

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 ...


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

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.[...


10

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) +---+ | ...


9

Only the outermost ORDER BY will guarantee order Any intermediate or internal ORDER BY is ignored.This includes ORDER BY in a view There is no implied order in any table There is no implied order from any index (clustered or not) on that table Links "Sorting Rows with ORDER BY" (MSDN) ORDER BY guarantees a sorted result only for the outermost ...


9

It isn't possible to calculate relevance with the LIKE predicate. For SQL Server (which from previous questions I believe is your platform?) you'll want to look at full-text search which supports scoring/ranking results by relevance.


9

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 ...


9

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) ...


8

Databases do not return rows in a given order unless you supply an ORDER BY clause in your query, thus making the INSERT "order" meaningless. The order of a SELECT * FROM MYTABLE; query is undefined. Apologies for the simple answer!


8

I had a simpler repro in mind: CREATE TABLE #x(z CHAR(1)); CREATE TABLE #y(z CHAR(1)); INSERT #x SELECT 'O'; INSERT #x SELECT 'R'; INSERT #x SELECT 'D'; INSERT #y SELECT 'E'; INSERT #y SELECT 'R'; SELECT z FROM #x UNION ALL SELECT z FROM #y; Results: O R D E R Now add an index: CREATE CLUSTERED INDEX z ON #x(z); SELECT z FROM #x UNION ALL SELECT ...


8

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 ...


8

Query While your solution is clever, it's poison for performance because the value to order by has to be computed separately for every row. More importantly, your query cannot use a plain index. I suggest to move the trick to the LIMIT clause and use UNION ALL. This way the query gets cheaper overall and can use an index (which nukes competitors that can't)...


7

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 ...


7

First order by just the date, then prioritize the values where the timestamp is not midnight over those where it is. ORDER BY CONVERT(DATE, col), CASE WHEN CONVERT(DATE,col) = col THEN 2 ELSE 1 END, CONVERT(TIME, col);


7

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). That ...


7

Select FullName, Login FROM [User] ORDER BY CASE WHEN FullName IS NULL THEN 'ZZZZZZZ' ELSE FullName END, login asc


6

Just out of curiosity, can you try this version? It may trick the optimizer to use the same indices that the subqueries would use separately: SELECT * FROM (SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt AS zeit, 'hp' AS source FROM is_log AS l WHERE l.account_id = 730 ORDER BY l.zeitpunkt DESC LIMIT 10) AS a UNION ALL SELECT * ...


6

I guess you want the results in ascending order but with 9999 always first? Select EMP From Emp Order By Case When Company = 9999 Then -1 else Company End, Case When Office = 9999 Then -1 else Office End;



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