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

learn more… | top users | synonyms (1)

3
votes
1answer
33 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
107 views

Sorting Order by GETSORTED

In my sybase 12, when I look at the showplan of a select distinct query, I see GETSORTED in the showplan's output. I've searched through the entire internet (YES!) and I can not seem to find a link ...
1
vote
1answer
12 views

MariaDB / mYSQL Order BY PrimaryKey DESC vs DESC set by Default

What do you guys think about: After each query, I put "Order BY PrimaryKey DESC" VS I set "PrimaryKey Sort by DESC Default" and each query I don't need to put Order By there. Which one will be ...
0
votes
1answer
50 views

Mysql: slow down with order by, global database problem

i create nasty query for testing reasons. Only for speed testing. EXPLAIN SELECT m.post_id FROM wp_postmeta m LEFT JOIN wp_postmeta mi ON m.`post_id` = mi.`post_id` LEFT JOIN wp_postmeta md ON (md.`...
0
votes
1answer
25 views

How can I speed up an ASC sort on a column that only holds an integer between 0 and 9 across multiple millions of rows?

CREATE TABLE foo ( id TEXT NOT NULL, bar INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (id) ); CREATE INDEX foo_bar_idx ON foo(bar) WHERE NOT bar = 0; I can sort on the bar column in DESC order ...
1
vote
1answer
33 views

ALTER TABLE engine from MyISAM to InnoDB: why does mysqldump --order-by-primary help me?

I found an interesting mysqldump option in MySQL 5.7 Reference Manual. In internet there are no further details and explanations. --order-by-primary Dump each table's rows sorted by its primary ...
4
votes
2answers
333 views

How do I order by NULLS LAST if my database doesn't support it?

Select FullName, Login FROM [User] ORDER BY FullName desc, login asc Results FullName Login ----------------------------- ------------------------------ M...... ...
0
votes
3answers
124 views

Oracle union default sort is weird [closed]

As you know union removes duplicated rows. I thought it removes from the latter ones, but it doesn't. I found out that if there's no order by clause, Oracle sorts the merged dataset by the first ...
1
vote
1answer
30 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. ...
2
votes
3answers
112 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: --------------------------...
0
votes
1answer
33 views

MySQL BETWEEN command - performance (order)

I want to create a table which will contain approx 20,000 records of three fields each. Two of the fields will contain numeric values. I want to be able to issue a SELECT BETWEEN command. One record ...
0
votes
1answer
43 views

DB2 simple order by query

I am very new to DB2.have installed Express C version and facing some problem while running a simple query. SELECT A,B,C,D,E,F,G, H FROM TABLE_NAME where A=9999 ORDER BY D Note: A through H ...
1
vote
0answers
23 views

CTE ordering guarantees [duplicate]

I'm starting to make heavy use of CTEs for my postgres based application, and am wondering if selecting from a previous CTE statement guarantees any ordering? E.g. is the order of rows for this query ...
0
votes
1answer
47 views

MYSQL ORDER BY Performance

We have a query like this: SELECT PROJECT.PROJECTID, REQUEST.REQUESTID, SAMPLE.SAMPLEID,SAMPLE.OTHERSAMPLEID, ILLUMINASEQPROTOCOL2.SAMPLEID FROM ILLUMINASEQPROTOCOL2, PROJECT, SAMPLE, ...
5
votes
1answer
233 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 ...
3
votes
1answer
61 views

ORDER BY count and Bitmap Heap Scan

I have a table with hits: Column | Type | Modifiers --------------+-----------------------------+--------------------------------...
1
vote
1answer
30 views

Ordering in subqueries

This is my query: select locid,tlid,locdescription from ( select locid, tlid, locdescription from trip_log left join trip_log_det on tlid=tldtripid left join locations on tldlocation=locid ...
1
vote
1answer
71 views

Ordering in postgresql

I'm facing an issue whereas I do not understand. Running PostgreSQL on mac and have this query: select tldid, tldaction, to_char(tldactiondate, 'dd/mm/YYYY') as tldactiondate, ...
1
vote
1answer
134 views

Subquery ORDER BY doesn't work on MySQL 5.6, but works on 5.5

I try to join 3 tables (products, prices, categories) together to get a result of latest & lowest prices from the joined tables. I was able to get the expected result on MySQL 5.5, but after ...
1
vote
1answer
56 views

Best way of order processing

I would like to write a procedure for order processing Single Order can have multiple items, how can I process the order ? Consider below JSON is an example: { "Order": { "OrderId":...
4
votes
1answer
70 views

Pull more records based on IDs in comma separated list returned by GROUP_CONCAT [duplicate]

Is it possible to pull more records based on IDs in comma separated list return by GROUP_CONCAT? Is a LEFT JOIN possible here? If so, how can it be done. I am constantly failing at figuring this out, ...
1
vote
1answer
38 views

If I use varaibles in the order by clause, is there any guarantee the result will be sorted?

I have a table like this: // Table1 +----+--------------------+------+------------------+---------+-----------+ | Id | QuestionOrAnswer | Type | AcceptedAnswerId | related | timestamp | +----+------...
3
votes
1answer
74 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 ...
4
votes
3answers
231 views

Does an order by clause have a negative effect on performance?

I couldn't find the answer by googling. I only over heard a senior dev telling a junior dev that using an order by clause has a negative effect on performance and he shouldn't use it(Oracle). In ...
0
votes
1answer
55 views

How to ORDER a big table?

I have a simple but big table (tens of millions of rows). The performance is quite good except when I need to order the table by the column with format datetime. A simple SELECT query to fetch the ...
4
votes
2answers
194 views

Same record shows up multiple times in ordered paginated query

Some records show up on multiple result "pages" with the following query: SELECT `description`, `wallpaper` FROM `bbr_bar` WHERE `country_id` = '2510769' ORDER BY `online` DESC ...
1
vote
2answers
102 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), ...
2
votes
5answers
309 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 ...
0
votes
1answer
50 views

order by case with multiple order criteria

I would like to know how to use multiple order columnes with case. For example case 1, order by FirstName, GivenName case 2 order by GivenName, FirstName. I tried this, but it looks like the colon ...
0
votes
2answers
124 views

PostgreSQL How to optimize a query with ORDER BY and LIMIT 1?

I have the following PostgreSQL schema: CREATE TABLE User ( ID INTEGER PRIMARY KEY ); CREATE TABLE BOX ( ID INTEGER PRIMARY KEY ); CREATE SEQUENCE seq_item; CREATE TABLE Item ( ID ...
-1
votes
1answer
25 views

Setting An Order For Specific Fields Using SELECT and ORDER BY

So I'm writing a query to show the hire_date, job_id and last name where the last name= 'Matos' and 'Taylor and the hire_date is in ascending order. When executing this code: SELECT last_name, ...
2
votes
2answers
167 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, ...
1
vote
2answers
277 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 ...
4
votes
2answers
233 views

Sort a varchar type column

In my table VARCHAR data type column called CartonNo, that contain either numbers or number with letter it depends on. I want it to be sorted numerically, this would be an acceptable ascending sort ...
3
votes
2answers
88 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
1answer
55 views

Order by in SP with input param

I have a table with 2 columns: CREATE TABLE test( id int, name nvarchar(50) ) go insert INTO Test(id, name) VALUES (1, 'aa') insert INTO Test(id, name) VALUES (2, 'bb') insert INTO Test(id, name) ...
8
votes
2answers
226 views

Is there a way to optimize sorting by columns of joined tables?

This is my slow query: SELECT `products_counts`.`cid` FROM `products_counts` `products_counts` LEFT OUTER JOIN `products` `products` ON ( `products_counts`.`product_id` = `products`.`id` ) ...
4
votes
2answers
220 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
194 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 ...
1
vote
1answer
486 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[]...
8
votes
3answers
900 views

NOLOCK hint changes order of records returned

There is a clustered index on a table Client field LastName. When I simply dump all records from the table, they appear in the alphabetical order unless (nolock) hint is used as in the query in ...
4
votes
2answers
138 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
1answer
110 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. ...
0
votes
3answers
29 views

I want a select to give me id's that are not in a consecutively order

I have an Oracle db with a table like this: +----+-------+ | ID | INDEX | +----+-------+ | 1 | 0 | | 1 | 1 | | 2 | 4 | | 2 | 5 | | 2 | 6 | | 3 | 0 | | 3 | 1 | | 3 |...
2
votes
1answer
35 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
3answers
94 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 +------...
1
vote
2answers
7k 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 ...
5
votes
4answers
463 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 ...
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 ...
-2
votes
1answer
512 views

Sorting in dd/mm/yyyy format group by date

select CONVERT(nvarchar(15),TransDate,103) as TransDate, isnull(SUM(Convert(int,Amount)),'0') as Amount from HMS_DiagnosisTransactions where TransDate between '05/01/2015' and '07/24/2015' ...