Questions tagged [order-by]

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

43 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3
votes
1answer
58 views

How can I get the last items by date desc then by id desc?

I have a employee course table which holds the current record for each of my employees who have some courses that they have taken. Then I have a history that keeps a static version of the save data ...
3
votes
2answers
121 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
0answers
104 views

Postgres / slow query / order by

I'm joining two tables, filter out results by second table and order results by first table's ID. The query is super-slow on Postgres Database. I see the issue is in the ORDER BY statement. But ...
2
votes
0answers
2k views

Performance hits with MySQL Left Joins and Order By

There are a lot of questions out there regarding this issue, but most answers seem to be very case-specific. I've tried to make this general so it'll help others too. The Issue We have a smallish ...
2
votes
0answers
1k views

group by and order by making query very slow in mysql?

Query works fast: select SQL_NO_CACHE users.id, users.name from useres left join user_statistics on users.id = user_statistics.user_id where users.is_deleted = 0 group by users.id ...
2
votes
1answer
950 views

ORDER BY clause with Alias

I am trying to get the sum of the market values for each given CPTY. Each CPTY can have several market values. In my output list, i want to see the top 5 CPTYs, ordered by the sum of their market ...
1
vote
0answers
44 views

Postgres Ignores Index When Ordering By Column From Joined Table

I am trying to figure out why Postgres is not using index when querying with ORDER BY clause with a SELECT query and 2 simple LEFT JOIN statements. I will explain my relevant database structure first: ...
1
vote
1answer
28 views

Optimizing multi-criteria sort query

I want to efficiently sort a table in PostgreSQL based off of two criteria. Here is my table structure: CREATE TABLE shapes ( shape_id character varying, shape_pt_lat double precision, ...
1
vote
2answers
37 views

A SQL query optimzation issue with ORDER BY

An SQL query optimzation issue with ORDER BY Current status I have a database of Chess games and their corresponding moves (stored as strings called fens). I have two main tables 'Game' and '...
1
vote
1answer
1k views

MYSQL - How to optimize VIEW performance?

Total profiles - 100000 Each profile is connected to one or more platform Each profile have 50 Tags Each profile have 4 themes I have a user view (total users around 100000) where I am joining all ...
1
vote
1answer
44 views

How to order by then start at first non null value and select a limit for number of values

I have an environmental logger and I am trying to select every 5th row starting at where outsidetemp is not null (I want to include the null values after the first not null value). Is there a way to ...
1
vote
0answers
31 views

PostgreSQL - SELECT query with ORDER BY and LIMIT 4 very slow, same query much faster with LIMIT >= 40

I've got following sample query from our Postgres DB: SELECT * FROM advertised_car a0_ LEFT JOIN car_check c1_ ON a0_.car_check_id = c1_.id WHERE c1_.status = 'ACCEPTED' ORDER BY a0_.id LIMIT 4; ...
1
vote
0answers
52 views

Exclusive and Inclusive ranking queries in Postgres 9.6

I'm trying to build a filtering/ranking search that would let me order and optionally filter out people based on a 1-to-many relationship with job "focuses". The tables are shown at the bottom. A "...
1
vote
0answers
268 views

ORDER BY doesn't use an index on SQL Server if CASE WHEN is present

Firstly, I apologize - I couldn't create an example SQL Fiddle, because the site doesn't seem to be working right now. Now, onto the problem... I'm getting an inconsistent sort order on SQL Server ...
1
vote
0answers
2k views

Mysql query to get all different conversations for and to same user

This a simple chat table id| fromUserId| toUserId| message Let's take dummy data John (45), Jane(46) and Mary(47). Say John chats with both Jane and Mary at the same time. John (45) started the ...
1
vote
0answers
36 views

Case-folded sorting of output columns containing expressions

Using the following query as simple example, SELECT l.id, string_agg(lc.country, ' ' ORDER BY lc.country) AS countries FROM licenses l JOIN license_countries lc ON ...
1
vote
1answer
168 views

How to sort query filename output like Windows explorer?

i would like to order my query output the way Windows Explorer does. First special characters like _, then the numbers, and the letters last. _ttt.bmp 0.gif bbb.png
1
vote
0answers
66 views

Custom cycling order in MySQL

Having following Foo table: id | name | group ===|=========|====== 1 | "John" | 1 2 | "Paul" | 1 3 | "Marry" | 1 4 | "Josh" | 1 5 | "Mike" | 2 6 | "Kevin" | 2 7 | "Ramon" | 2 8 | "Kim" ...
1
vote
0answers
327 views

PostgreSQL query slow when using tsvector and ORDER BY despite having index

Running on a large database (around 1.5G) The following query takes about 70 seconds to run, SELECT x FROM DBText x WHERE x.status = 2 AND x.publishedTime < 4574590406397 AND x.service = '...
1
vote
0answers
61 views

Slow MySQL Query when Ordering

This query takes around 2 seconds when the order by is included, without the order it takes less than a tenth of a second. The field that is doing the sorting, qc_products.addedDate, is an index and ...
1
vote
0answers
144 views

MySQL query with order by decreased performance

Here is my schema: CREATE TABLE `UserMst` ( `UserID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `EMailID` varchar(50) DEFAULT NULL, `UserName` varchar(20) DEFAULT NULL, `FullName` ...
1
vote
0answers
767 views

MySQL GROUP BY and ORDER BY giving inconsistent results

I'm currently working with WordPress, and I've added a new meta value which is a number between 0 and 30. Zero is ignored and up to 30 need to be organised in order and only the latest (based on the ...
0
votes
1answer
51 views

Slow query Mysql with Order By and JOIN

I've been investigating an issue with a slow query when trying to get the last 50 comments of a user. To give some context the database size is 9 TiB and the COMMENT table has about 7 billon of rows. ...
0
votes
2answers
37 views

Sort index doesn't work for MySQL

I have a table with more than a million records. I get the data by using LIMIT, but also ORDER BY. The latter causes a huge performance problem. My query ends with this: ... ORDER BY `record`.`...
0
votes
1answer
18 views

Why is below 2 query generating different execution plan in MYSQL?

Only difference in the below query is the order by column. The resultset of both query is same. However, the 2nd query execution plan shows 'Using temporary; Using filesort' for ordering where as for ...
0
votes
1answer
39 views

Sort results by number of matching words

I have a table (called sentences) in which I have some sentences. I want people to be able to search it with multiple words, which would make the SQL query look like this: SELECT content FROM ...
0
votes
0answers
117 views

MySQL result ORDER BY reverse order?

My query looks like .. SELECT uid FROM user WHERE plz IN(24718,32326,32329,32328) ORDER BY FIELD(plz,24718,32326,32329,32328), uid ...
0
votes
1answer
44 views

ORDER BY with IF-ELSE statement (DESC doesn't work)

Descending sort type does not work with this syntax, please help. <?php require_once("../connection/conn.php"); $compare = $_GET["sortfield"]; if(isset($_GET["sortfield"])){ if(isset($_GET["...
0
votes
2answers
30 views

Does reordering table rows reduce the time for subsequent ordering with the ORDER BY clause?

Is there any run-time advantage gained by changing the order of a table's rows to match the expected ordering of the ORDER BY that is in a slow select? (assuming unique alphanumeric index and no auto-...
0
votes
1answer
24 views

MySQL query help

I need a query that returns the number of liked messages and orders them by the number of likes (from most to least). I have tried: SELECT users.user_id, messages.author, messages....
0
votes
0answers
30 views

Postresql using subquery select in order by clause

Is it possible to order by a selected columnname? SELECT * FROM tablex ORDER BY quote_ident(( select column_name from information_schema.columns where table_name = tablex AND column_name LIKE '...
0
votes
0answers
89 views

postgres: join tables, order by GREATEST

Having the following table structure table1 id: BIGSERIAL updated_at: TIMESTAMP WITHOUT TIME ZONE DEFAULT now() ... CREATE INDEX table1_updated_at ON table2(updated_at); table2 table1_id: ...
0
votes
1answer
50 views

Mysql Performance Issue with Group By/Order By

I am using Mysql 5.7 I have table which having 7006500 rows. My query performing group by and fetching row which has maximum count with each group on column which is already indexed but still takes ...
0
votes
2answers
85 views

Get unique ids based on order by another column using group by with row_number

Here is a link to the SQLfiddle. For pagination purpose, I am first executing a query to execute if's and order by's and return a list of ids, using which I query for all the data with contained lists ...
0
votes
1answer
26 views

Select data in steps and ranges

I have the following GPS data. I'm trying to get trips made by the user based on the engine status, and ordered by date where engine=1 is a engine start and engine=2 is a engine off event, so every ...
0
votes
1answer
62 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, ...
0
votes
1answer
69 views

Filter and then sort products by availability

I have table with products. Each product can be avaible in 1+ suppliers, so product can be present multiple times. products - id_productSupplier (primary key) - id_product (foreign key) - ...
0
votes
1answer
33 views

MySQL order of query

My query return like this. 12 14 14 99 78 78 54 I want result to be sorted so that no same number comes consecutively in the result. The result that I want is like this. 12 14 99 14 78 54 78
0
votes
0answers
33 views

Which of these two queries is better - can be optimized more?

With regards to the usage of the IF statement, which is better to use? Query #1: SELECT whatever FROM auctions AS a LEFT JOIN ............... WHERE ............... ORDER BY IF(a....
0
votes
0answers
2k views

Using an enum as part of an index definition

When I use an ENUM column in a table the ORDER BY uses the ordinal of the ENUM not its value. I wasn't aware of that. So my question is what are the subtle issues when an ENUM is part of an index ...
-1
votes
1answer
63 views

Count comma seperated values in mysql ORDER BY clause

I have a table name products: id - pro_name - ip - date_created 1 - abc1 - 27.106.121.23,27.106.121.86 - 2018-08-15 13:32:00 2 - ...
-1
votes
2answers
2k views

MySQL Very Slow Performance Using Order by Clause

I have a one table with millions of entry.Below is table structure. CREATE TABLE `useractivity` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `userid` bigint(20) NOT NULL, `likes` bigint(20) DEFAULT ...
-1
votes
1answer
93 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.`...