A clause used in SQL SELECT statements to sort query results.

learn more… | top users | synonyms

26
votes
5answers
12k views

Why does ORDER BY not belong in a View?

I understand that you cannot have ORDER BY in a view. (At least in SQL Server 2012 I am working with) I also understand that the "correct" way of sorting a view is by putting an ORDER BY around the ...
12
votes
1answer
9k views

Order by column should have index or not?

I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How ...
11
votes
1answer
14k views

Parent-Child Tree Hierarchical ORDER

I have to following data in SQL Server 2008 R2. SQLFiddle Schema: CREATE TABLE [dbo].[ICFilters]( [ICFilterID] [int] IDENTITY(1,1) NOT NULL, [ParentID] [int] NOT NULL DEFAULT 0, ...
11
votes
2answers
3k views

Must an index cover all selected columns for it to be used for ORDER BY?

Over at SO, someone recently asked Why isn't ORDER BY using the index? The situation involved a simple InnoDB table in MySQL comprising three columns and 10k rows. One of the columns, an integer, ...
9
votes
4answers
670 views

Arbitrarily ordering records in a table

A common need when using a database is to access records in order. For example, if I have a blog, I want to be able to reorder my blog posts in arbitrary order. These entries often have lots of ...
8
votes
5answers
4k views

Oracle sort varchar2 column with special characters last

How can I sort in Oracle a Varchar2 or NVarchar2 column to be in my own custom defined order. Or are any existing options available that will put letters first, then numbers, then all special ...
8
votes
3answers
2k views

What are the alternatives for an ORDER BY clause in a View?

This question just had to be in this site :) ORDER BY is forbidden to use in a view, as I understood because of the possibility for multiple order by's when using this view. I know that there are ...
7
votes
2answers
760 views

Does DB2 order data by the primary key

I've recently had a discussion with a colleague who was pushing to remove order by clauses from a production query because the order by column was the same as the primary key. After a lengthy ...
7
votes
4answers
5k views

How do I do a complex GROUP BY in MySQL?

I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to be able to group rows together that have an equal key, but I don't want ...
6
votes
1answer
414 views

Order by bug on SQL Server 2005 vs SQL Server 2012

I have a query generated by entity framework that returns wrong results on a SQL Server 2005 database (Microsoft SQL Server 2005 - 9.00.5000.00 (X64)) but good on SQL Server 2012 (Microsoft SQL Server ...
6
votes
3answers
2k views

Query best possible matches and order them

I'm trying to write a query along these lines: select * from tbl where col1 = 1 and col2 = 2 and col3 = 3 order by ... ; I want first all the results where all 3 WHERE ...
6
votes
1answer
880 views

adding an order by to this query returns faster than without, why?

Any ideas on why adding a sort to this query returns considerably faster than without the order by? I would expect the opposite so what could make this happen? SELECT TOP (500) ...
5
votes
2answers
3k views

Is SELECT ROW_NUMBER() guaranteed to return results sorted by the generated row numbers?

E.g. consider the SQL query: SELECT A.[Name], ROW_NUMBER() OVER(ORDER BY A.[Name] ASC) FROM [FooTable] AS A Here I observe the results being returned sorted by A.[Name]. If I change the ...
5
votes
1answer
552 views

How does SQL Server order results when joins are used?

How does SQL Server figure out the order of the records in the result set of query execution? I am trying to make heads or tails of it but find myself scratching my head. When I change the fields I ...
5
votes
3answers
7k views

How to make conditional ordering for two or more columns

IN MSSQL 2005 I am writing one query with conditional sort and my problem is I do not know how I can sort condidional using two columns. if i wrte code like this it is working normaly select * from ...
5
votes
2answers
718 views

List of all missing order numbers that are not in the table

Our workplace has started to scan all documents rather than manually keep them in archives etc. On average we are scanning 1,000 documents per time (this happens about 30 times per day). Each time ...
4
votes
4answers
344 views

Why index is not used with order by?

I trying to get info about why MySQL does not using my index when I create a inner join and trying to ORDER BY on the end. I have my SQL query here: SELECT * FROM product p INNER ...
4
votes
2answers
13k views

CASE ORDER BY with multiple columns, and different sort options

I am attempting to use a T-SQL CASE ORDER BY in a stored procedure where I am passed @OrderBy parameter as a TINYINT. @Orderby = 1 Then Date column should be ASC @Orderby = 2 Then Date column ...
4
votes
1answer
11k views

MySQL: Optimize UNION with “ORDER BY” in inner queries

I just set up a logging system which consists of multiple tables with the same layout. There is one table for each data source. For the log viewer, I want to UNION all the log tables, filter them ...
4
votes
1answer
4k views

How do I make MySQL auto increment in random order?

I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to create a new column for each key that would be an integer such that ...
3
votes
2answers
4k views

Sort by match of LIKE

I wondering how I can implement SQL to get results sorted by best match of a like predicate. I have 100K articles in the database and when user call some items by part of name. I want to show the ...
3
votes
4answers
4k views

MySQL : Conditional ORDER BY to only one column

Hello and thanks for taking time to read this question. I am using MySQL, and I want to sort results using ORDER BY to one specific column, but the results must be ordered according an specific ...
3
votes
2answers
79 views

Extremely slow query on indexed column

I'm getting a extremely slow query on an indexed column. Given the query SELECT * FROM orders WHERE shop_id = 3828 ORDER BY updated_at desc LIMIT 1 explain analyze returned: QUERY PLAN ...
3
votes
3answers
2k views

Does ORDER BY on a clustered key effect performance?

Say I have a table clustered on PrimaryKey, and in all cases I want my results to be ordered by PrimaryKey so I additionally always ORDER BY PrimaryKey in all queries. Does this ORDER BY affect ...
3
votes
1answer
2k views

ORDER BY items must appear in the select list […]

Using Microsoft SQL Server 2008, I get the following error. Msg 104, Level 16, State 1, Line 43 ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or ...
3
votes
1answer
145 views

Should all queries where you expect a specific order, include an ORDER BY clause?

In order to better understand the SQL Server query processor, I've been thinking about how the ORDER BY clause works, and how SQL Server provides results. It appears SQL Server will provide results ...
3
votes
2answers
1k views

Database Implementations of ORDER BY in a Subquery

I am using an application (MapServer - http://mapserver.org/) that wraps SQL statements, so that the ORDER BY statement is in the inner query. E.g. SELECT * FROM ( SELECT ID, GEOM, Name ...
3
votes
2answers
2k views

MySQL: Is it possible to order a query by a specific letter using ORDER BY?

What I'd like to do is order my query so that the results show every row found, but are ordered by showing results that begin with the letter 'D' first. Is this something that is possible to do with ...
3
votes
3answers
4k views

Want to order on two columns but not the usual way

I have a table as below ID userID Date 1 2273 22/08/2011 2 2274 24/08/2011 3 2275 26/08/2011 4 2273 26/08/2011 5 2273 26/08/2011 6 2271 26/08/2011 And want result as ...
3
votes
1answer
1k views

Why is the ORDER BY clause in a view ignored as soon as it's called with a WHERE claue?

I have a view that joins several tables, just to simplify a frequently called query. This view has an ORDER BY clause on a calculated field (the order will never be different). (on SQL Server Express ...
3
votes
2answers
5k views

MySql: Delete all rows greater than n entries ordered by datetime

Can someone help me with a MySql query to delete all rows greater than n entries ordered by date? I.e. say I have 1200 rows of data with a timestamp column. I need to order it by date and preserve ...
3
votes
2answers
6k views

MySQL not using indexes when joining against another table

I have two tables, the first table contains all articles / blog posts within a CMS. Some of these articles may also appear in a magazine, in which case they have a foreign key relationship with ...
3
votes
1answer
137 views

Sequential joining of tables in order

Table albums has (among other field) field id. Table photos has column id, field album which is a foreign key referring to album id and some other fields (which are irrelevant for the question I ...
3
votes
1answer
68 views

Special ordering

I have a hierarchical query but I don't manage to order it as I want. I have a column named sequence that indicates how to order it but the data is kinda mixed up and I cant do it properly. This a ...
3
votes
3answers
632 views

MySQL: ORDER BY useless?

CREATE TEMPORARY TABLE tt; INSERT INTO tt; many times SELECT FROM tt ORDER BY id LIMIT ?,1000; The temp table is created at the beginning of the script having an "id" auto_increment column (and ...
3
votes
1answer
429 views

Using an index for both asc and desc on a string column

I know a trick to make order by queries faster when we want to order by asc and desc. For integers store a negated value. So if I store -7,-11,-8,-5,-1,-2 the order by asc will give -11, -8, -7, 5, ...
3
votes
1answer
59 views

How is ORDER BY logically Processed

Take this example: SELECT SalesOrderID, CustomerID, SalesPersonID, OrderDate FROM Sales.SalesOrderHeader ORDER BY OrderDate, SalesOrderID DESC OFFSET 0 ROWS FETCH NEXT ...
3
votes
1answer
235 views

Bayesian ranking

I have the following table in my database: ╔════════╦═════════════╦═══════════════╗ ║ Name ║ total_stars ║ total_reviews ║ ╠════════╬═════════════╬═══════════════╣ ║ Item A ║ 27 ║ ...
3
votes
1answer
134 views

Does Postgres preserve insertion order of records?

For example when I'm using query which returns record ids INSERT INTO projects(name) VALUES (name1), (name2), (name3) returning id; Which produce output: 1 2 3 Will this ids point to ...
3
votes
1answer
294 views

Unused index in range of dates query

I have a query that is not using existing indices and I do not understand why. The table: mustang=# \d+ bss.amplifier_saturation Table ...
3
votes
1answer
1k views

How do I sort the results of a recursive query in an expanded tree-like fashion?

Let's assume you have a nodes tables like this: CREATE TABLE nodes ( node serial PRIMARY KEY, parent integer NULL REFERENCES nodes(node), ts timestamp NOT NULL DEFAULT now() ); It ...
3
votes
1answer
2k views

Update one table from another table while sorting that table based on one column

This is the problem I'm trying to figure out in MySQL. We have an old table contains some forms submitted by our users. Somehow, the previous decision was each time a user comes to this survey, a new ...
3
votes
1answer
763 views

How to order query results by tag weight in IN('tag1', 'tag2', 'tag3')

Consider a simple query as SELECT * FROM posts JOIN tags USING(post_id) JOIN tag_map USING(tag_id) WHERE tags.tag IN('tag1', 'tag2', 'tag3') tag_map is a many-to-many relationship between tables ...
2
votes
3answers
565 views

How to ORDER BY typical software release versions like X.Y.Z?

Given a "SoftwareReleases" table: | id | version | | 1 | 0.9 | | 2 | 1.0 | | 3 | 0.9.1 | | 4 | 1.1 | | 5 | 0.9.9 | | 6 | 0.9.10 | How do I produce this output? | id | ...
2
votes
2answers
198 views

Order of returned record with IN statement postgres

I know that the order of returned records is not guaranteed with the IN statement for Pstgres. For example if I do this: SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588) ...
2
votes
3answers
84 views

Change the Row Order to alphabetical then numeral

I have a table with a username as unique & primary key. I ran this Query to order them in ascending order ALTER TABLE `table` ORDER BY `Username` ; So the rows are ordered like this ...
2
votes
2answers
392 views

SQL order by query results in any arbitrary (but reproducible) manner

For testing purposes I need to get some data from a set of database tables in any arbitrary (but reproducible) order. The idea being that I can later compare two runs using a textual diff tool. Is ...
2
votes
3answers
162 views

Ordering by date and other columns

I have a one table Q&A like below: CREATE TABLE `totaalconceptterreinonderhoud`.`acties` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID', `type` int(11) NOT NULL DEFAULT '3' ...
2
votes
1answer
2k views

Order by alphabet and then by numbers

I have records like A5 A4 Z1 B2 C7 C1A C11A B1 B4 I want them to be sorted in this manner A4 A5 B1 B2 B4 C1 C11A C7 Z1 using the ORDER BY clause. I want them to be sorted by alphabets ...
2
votes
2answers
2k views

Slow MySQL query with Order and Group by

I have a query that is taking a long time to execute (1.13s). The two tables user_articles and articles have around 25,000 records. This is the query: SELECT `user_articles`.* FROM `user_articles` ...