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

learn more… | top users | synonyms (1)

1
vote
3answers
83 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
31 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
27 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
207 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
56 views

ORDER BY count and Bitmap Heap Scan

I have a table with hits: Column | Type | Modifiers ...
1
vote
1answer
29 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
70 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
121 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
49 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": { ...
4
votes
1answer
63 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
58 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
140 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
49 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
139 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
85 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
278 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
43 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
103 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
125 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
220 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
182 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
213 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
188 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
163 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
334 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 ...
8
votes
3answers
869 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
120 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
106 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
34 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
93 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
5k 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
445 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
42 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
458 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' ...
0
votes
1answer
141 views

Set in mySQL a predefined order for SELECT queries without using the ORDER BY clause

I have a MySQL database and users access it through an old legacy software (OLS) the company bought years ago. OLS connects to MySQL through MySQL ODBC driver. There is no way to change OLS in recent ...
1
vote
3answers
830 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
97 views

Get the nth distinct column value ordered by another column

I want to get the row with the nth distinct value in a column, as ordered by another column. In more real terms, I want the 1000th distinct email_address as ordered by created_at, and the ...
3
votes
1answer
1k 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 ...
2
votes
1answer
330 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 ...
1
vote
2answers
42 views

Custom ORDER BY

Are there other ways to use ORDER on my results? 20150507094958A DISPOSED 2015-05-07 09:50:00.853 Samuel MONEY 20150507094958A DISPOSED 2015-05-22 09:25:00.000 Jeff AUTOMOBILES ...
2
votes
1answer
80 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
0answers
47 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, ...
5
votes
2answers
784 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 ...