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

learn more… | top users | synonyms (1)

11
votes
2answers
4k 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, ...
4
votes
1answer
649 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 "bss....
3
votes
1answer
148 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 ...
5
votes
1answer
244 views

In PostgreSQL is the row ordering preserved in functions and CTEs?

In SELECT statements the ordering of the returned rows is not guaranteed if the ORDER BY clause is not specified. This is true for "normal" tables. Is this also true for ordered tables generated ...
4
votes
2answers
143 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 ---...
2
votes
2answers
4k views

MySQL order by - one column conditional on another column

In MySQL I have a table (simplified for this question) CREATE TABLE `projects` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `priority` int(11) DEFAULT '1000000', `...
6
votes
1answer
722 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 ...
14
votes
1answer
24k 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, [...
13
votes
4answers
1k 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 ...
6
votes
1answer
1k 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) r.cID,r.aField,a....
13
votes
2answers
14k 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 ...
8
votes
2answers
1k 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 ...
8
votes
4answers
6k 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
3answers
12k 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 ...
6
votes
2answers
1k 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 ...
5
votes
2answers
18k 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 ...
5
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 ...
4
votes
2answers
240 views

Use column values in ORDER BY and LIMIT clauses

I have this table in a Postgres 9.3 database: I need to use the content of columns order and limit to sort and filter this table. The table will be sorted by column first_name. The final result ...
4
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 ...
4
votes
1answer
209 views

Why does Postgres ORDER BY seem to halfway ignore leading underscores?

I have an animal table with a name varchar(255), and I've added rows with the following values: Piranha __Starts With 2 Rhino Starts With 1 0_Zebra _Starts With 1 Antelope _Starts With 1 When I run ...
3
votes
1answer
99 views

How to get 0 as array_length() result when there are no elements

I have a query like this: select id, array_length(users_who_like_ids,1) as ct from queryables order by 2 desc; But empty arrays (with no elements) sort on top. I'd rather have array_length() return ...
2
votes
1answer
226 views

Operator “~<~” uses varchar_pattern_ops index while normal ORDER BY clause doesn't?

Let's say I have a table with 1 000 000 records. Structure of table is: create table individual ( id serial primary key, surname varchar(128), "name" varchar(128), patronymic varchar(...
2
votes
2answers
454 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) ...