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

learn more… | top users | synonyms (1)

3
votes
1answer
1k 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, -2,...
3
votes
1answer
180 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 ask)....
3
votes
1answer
69 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
666 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
96 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 ...
3
votes
4answers
409 views

Mysql Order By affecting a Query with column alias, group by, and having

Description: I have a query with column alias, group by and having. It's getting altered when I add an order by. This strange behavior is only happening in 5.6.16 and 5.6.21 (which I was using in ...
3
votes
1answer
62 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
286 views

Bayesian ranking

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

Customize jsonb key sort order involving arrays

I have a table in PostgreSQL with some data: create table t2 ( key jsonb, value jsonb ); INSERT INTO t2(key, value) VALUES ('1', '"test 1"') ,('2', '"test 2"') ,('3', '"test 3"') ,('[]', '"...
3
votes
1answer
66 views

ORDER BY count and Bitmap Heap Scan

I have a table with hits: Column | Type | Modifiers --------------+-----------------------------+--------------------------------...
3
votes
1answer
2k views

Avoid joining to the same table multiple times

I have two tables: CREATE TABLE one ( id int4 primary key, p_id int4, k_id int4, c_id int4 ); CREATE TABLE two( id int4 primary key, p_id int4, k_id int4, t_id int4, pos int4 ); Table ...
3
votes
1answer
847 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 ...
3
votes
2answers
90 views

Optmizing ORDER BY

I'm new to MySQL and have written the following query (see below). PROBLEM: The query returns accurate data but takes about 40 seconds to execute. However, When I remove the ORDER BY part, it takes ...
2
votes
5answers
315 views

Order By DateTime With 00:00:00 Last

order by datetimecolumn orders a datetime column like this: datetimecolumn 2014-09-04 00:00:00 2014-09-04 00:00:00 2014-09-04 01:00:00 2014-09-05 00:00:00 2014-09-05 01:00:00 2014-09-05 02:00:00 ...
2
votes
2answers
9k views

How does ORDER BY FIELD() in MySQL work internally

I understand how ORDER BY clause works and how the FIELD() function works. What i want to understand is how the both of them work together to sort. How are the rows retrieved and how is the sort ...
2
votes
2answers
448 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
96 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
602 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
164 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
4k 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
134 views

Migrating SQL Server 2000 to SQL Server 2008

I'm migrating SQL Server 2000 to SQL Server 2008. I have many views. How do I check each views that if they have an order by clause? Thanks.
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` ...
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', `...
2
votes
3answers
113 views

Poor performance of JOIN, WHERE, ORDER BY combination (MySQL)

Goal: select 10 most recent rows from posts which are related to a specific user. There are two tables: posts (~5,000,000 rows) and relations (~8,000 rows). posts columns: --------------------------...
2
votes
2answers
194 views

ORDER BY indexedColumn ridiculously slow when used with LIMIT on MySQL

The MyISAM table has 4.5m rows. Query with just LIMIT returns fast; query with just ORDER BY returns fast; combining both it never finishes. I've also tried USE INDEX(indexedColumn, Latitude, ...
2
votes
2answers
804 views

In PostgreSQL is there a way to rank/weigh columns so as to order the results?

Let's say I am searching through three text columns. I don't just want results that match a given word or phrase, but I also want to weigh them (column A > column B > column C) - thereby ...
2
votes
1answer
2k views

SQL Server 2005: Full-text search, order by matches

Is it possible to do a full-text search in SQL Server 2005, ordering on the number of matches? For example, let's say I have the following products for sale. Tennis Racquets Tennis Balls Footballs ...
2
votes
2answers
165 views

Order by sub select

I have 3 tables: articles, paragraphs, notes An article may have many paragraphs, and a paragraph may have 0 or 1 note. Now I want to find all notes belong to an article: select * from notes where ...
2
votes
1answer
482 views

Why is ORDER BY LIMIT 1 so much faster than MAX?

Here are my two queries: select sts_in from sta_session where sts_user_id=2006 AND sts_sessid!='0jitkt80gg3avere03tqk4lhi6' order by sts_in desc limit 1; And select max(sts_in) from ...
2
votes
1answer
131 views

Making MySQL use primary index without hinting or forcing

I have the following db structure: CREATE TABLE `question` ( `id` int(11) NOT NULL AUTO_INCREMENT, `level_id` int(11) NOT NULL, `video_id` int(11) NOT NULL, `is_active` tinyint(1) NOT NULL, ...
2
votes
1answer
113 views

MySQL - Ordering 'featured' items first

I need to have the results of a query show featured items first for a given date before listing the day's other items. You can ignore the categories that are being pulled from another table. ...
2
votes
1answer
37 views

sorting output by incrementing IP in mysql

I want an incrementing list of IP #'s in a subnet to display on a php web page in a table. Right now, 10.1.2.3 would display after 10.1.2.10 in the sequence. My current query is this: SELECT * FROM ...
2
votes
1answer
84 views

MySQL Filesort/Order By Slow

I've been struggling with a query for the past few weeks and it now just appears as gobbly-gook to me when I try to optimize. The query runs - at about 2.3 seconds, but I know there has to be a more ...
2
votes
1answer
220 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
1answer
1k views

Why is MySQL order by performance poor within a single partition of a partitioned table?

I have to store some sequence numbered data in MySQL. I have about 300,000 data items per day for about a 10 year span. Let's say the table structure is just sequence number (a big int) and data (a ...
2
votes
1answer
3k views

MySql: #1221 - Incorrect usage of UNION and ORDER BY

I have the following query which is intended to retrieve the newest 3 comments for each post in an array of posts. SELECT * FROM comment AS tbl WHERE sscm_id IN ( SELECT sscm_id FROM comment AS t1 ...
2
votes
1answer
2k views

Slow complex query with group/order

I have a problem with a very important query in my application. It is very slow when adding group and order to it. So the full query is: SELECT m.id, m.title, m.title_text, m....
2
votes
0answers
49 views

How to order by fields of joined table while selecting for update?

For selecting some rows of table Fwt_Chipkarte for update I use the following SQL statement (IBM DB2): SELECT o.Archiviert, o.Id, o.IdComm, o.Kartennummer, o.Konto, o.LetzteAenderung, o....
2
votes
0answers
129 views

Schema for user drag and drop ordering

I'm trying to work out the schema for a system where users will be able to order items in an (unlimited length) list by drag and drop. Is there any kind of standard way of storing this ordering ...
2
votes
1answer
155 views

MySQL: Why queries with subqueries are much faster than single query?

I made few queries on my db lately First query: SELECT `table`.* FROM (`table`) WHERE table.id in(27172172,[...bunch of ids...],27171770) ORDER BY field (`table`.`id`, 27172172,27172168,[......
1
vote
1answer
527 views

weird sql server behavior when inserting and selecting

Today after one of the drives on our sql server ran out of space. The drive contained eventracker logs, so I didn't think it was a big deal, but then I noticed when I insert a record into a database, ...
1
vote
2answers
108 views

Special Oracle Order

I have a table (emp) with two columns in ORACLE: COMPANY OFFICE EMP ------------------------ 9999 00001 emp1 9999 00001 emp2 9999 00002 emp3 9999 00002 emp4 ...
1
vote
1answer
31 views

Order by x DESC - but only continuous rows

I have a table with an ID (integer) and a text field (the name). My current query looks like this: SELECT id, name FROM table WHERE id <= X ORDER BY id DESC X is an ID I get from another query. ...
1
vote
2answers
111 views

How to keep row ordering with DISTINCT command or how to add row number to cross apply

I have problem that after distinct, order direction of Variable column change. Here is example Data Source DECLARE @Table TABLE ( FirstName NVARCHAR(255), LastName NVARCHAR(255), ...
1
vote
3answers
1k views

Get rows changed between two dates (latest and previous)

I have a table _sync_history with this data: +----+----------------+---------------------+ | id | object_name | sync_time | +----+----------------+---------------------+ | 1 | territory ...
1
vote
2answers
288 views

How to order by alphanumeric codes in SQL Server [closed]

I have a table with some project codes. These codes are e.g.: B001, B002, Z15001, Z14001, P003,... I need this result table: Z15001 Z15002 ... B001 B002 ... C001 C002 ... Z14099 Z14098 Z14097 ... ...
1
vote
2answers
296 views

Selecting random rows from database initially while preserving that order later

I am building a quiz app, in which the student can login and can select the option of give exam. I want to show multiple choice questions to students randomly. I stored all the questions in one ...
1
vote
1answer
540 views

Query with limit and order by runs too slow

I have the following table: CREATE TABLE dpg2 ( account_id integer NOT NULL, tank_id integer NOT NULL, battles integer, dmg integer, frags integer, wins integer, recent_battles integer[]...
1
vote
2answers
45 views

Apply limit and conditions per grouped data

I am building an api endpoint to send statistics data that then is going to be plotted on a graph. The hardest part is being the query I need to execute. To sum up I want the first n most sold ...
1
vote
2answers
246 views

Filesort while using primary key for orderby

I have been breaking my head for the past two days now for this. CREATE TABLE `clients` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `guarantor_name` varchar(50) NOT NULL, `...