Tag Info

Hot answers tagged

21

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


19

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.


15

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


14

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


12

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


11

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


10

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


9

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


9

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


8

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


8

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


8

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.


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

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


7

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


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


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;


6

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


6

select id, name, v[1] as major_version, v[2] as minor_version, v[3] as patch_level from ( select id, name, string_to_array(version, '.') as v from versions ) t order by v[1]::int desc, v[2]::int desc, v[3]::int desc; SQLFiddle: http://sqlfiddle.com/#!15/c9acb/1 If you expect more elements in the ...


5

Break it out a little more: ORDER BY CASE WHEN @orderby = 1 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) END ASC, CASE WHEN @orderby = 2 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) END DESC, tp.lastname ASC, tp.firstname ASC You only need the sort order to change on the first field, so don't enclose the others in the CASE. It ...


5

Some options: Persist the sorted version of your data to a table via trigger, and use it. Use Oracle Locale Builder to build a custom sort order. (Caveat: I have never used this, so I do not know what gotchas may exist there.) You could then use the NLSSORT function with that custom sort order.


5

Have a look at below example DROP TABLE IF EXISTS products; create table products(pname CHAR(30),pdescription CHAR(30),price DECIMAL(10,2),manufacturer CHAR(30)); INSERT INTO products VALUES ('Toys','These are toys',15.25,'ABC'), ('Dolls','These are Dolls',35.25,'PQR'), ('DustPan','These are DustPan',75.25,'AZD'), ('Doors','These are ...


5

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


4

What about doing a little math against your ID column to dynamically generate the group? SELECT grp, FLOOR(id/10) AS id_grp FROM animals GROUP BY grp, id_grp This would give you groups of 10 based on the ID of the record. I used your animals table above to generate the data below. Sample data INSERT INTO animals VALUES ...


4

SELECT * FROM Mytable ORDER BY userID, Date I assume Date is really a date/time type and not varchar... Edit, after clarification: Untested SELECT M.* FROM ( --one row for each user SELECT MIN(Date) AS FirstUserDate, userID FROM MyTable GROUP BY userID ) foo JOIN MyTable M ON foo.userID = M.userID ORDER BY ...


4

SELECT* FROM mytable ORDER BY LOCATE(CONCAT('.',`group`,'.'),'.9.7.6.10.8.5.'); I took your sample data, loaded it into a table called mytable and ran it. Here are the results: mysql> use test Database changed mysql> drop table if exists mytable; Query OK, 0 rows affected (0.04 sec) mysql> create table mytable -> ( -> names ...


4

Use MySQL's find_in_set() function to do this. It is more concise but less portable than the CASE approach gbn proposed. For example: SELECT `names`, `group` FROM my_table WHERE `group` IN (9,7,6,10,8,5) ORDER BY find_in_set(`group`,'9,7,6,10,8,5'); Because it relies on string searching, find_in_set() is useful mainly for ordering on small sets of ...


4

If the sort order that you want to specify is already supported by Oracle, you can do this by ordering by the NLSSORT function - like so: ORDER BY NLSSORT(sorted_column, 'NLS_SORT = XDanish') -- Replace XDanish as appropriate You can find a list of supported sort orders here.


4

Along with JNK's answer, you could also consider: DECLARE @Example TABLE ( first_name NVARCHAR(50) NOT NULL, last_name NVARCHAR(50) NOT NULL, cert_end_date DATE NOT NULL, other_columns NCHAR(100) NOT NULL DEFAULT (N'') UNIQUE (cert_end_date ASC, first_name, last_name), UNIQUE (cert_end_date DESC, first_name, ...


4

SELECT col1, col2, col3, col4, col5, col6 FROM TableX WHERE col1 = 1 OR col2 = 2 OR col3 = 3 ORDER BY (CASE WHEN col1 = 1 THEN 1 ELSE 0 END) + (CASE WHEN col2 = 2 THEN 1 ELSE 0 END) + (CASE WHEN col3 = 3 THEN 1 ELSE 0 END) DESC, col4, col5, col6 or, for MS-Access: ORDER BY ...



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