Questions tagged [order-by]

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

Filter by
Sorted by
Tagged with
5
votes
1answer
158 views

Index Definition Order and ORDER BY Clause

So i was doing my morning blog reading and stumbled upon this fun exercise: https://www.erikdarlingdata.com/sql-server/lets-design-an-index-together-part-3/ Here is the query in question from the ...
3
votes
1answer
81 views

case sensivity incongruity

I'm trying to learn and understand collations and how postgresql compare and order strings but I'm finding an incongruity and I don't know what I'm missing. The query: SELECT datname, datcollate FROM ...
0
votes
1answer
25 views

Fill remaining LIMIT rows selection if conditions met are less than LIMIT number

I have a MySQL database with data in it. They all have locations (London, Liverpool, etc), I want a query to select 10 random rows with a location (for example London), and if there aren't enough to ...
0
votes
2answers
36 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`.`...
4
votes
1answer
77 views

Ordering rows by repeating pattern

Given a table in Postgres like CREATE TABLE t1 ( type text NOT NULL, value int NOT NULL ) where type could be A, B or C and given an arbitrary, repeating pattern like A B B C B B how can I query ...
2
votes
3answers
65 views

Ordering streets by their names and polysyllabic numbers in SQL query

In Oracle DB there is a table with some data: LAG HNR STREET 00020 44 Aachener Straße 00020 44/2 Aachener Straße 00020 44/1 Aachener Straße 00020 46 Aachener Straße 00020 46/6 Aachener Straße ...
0
votes
1answer
46 views

How to use ORDER BY and LIMIT inside JSON_AGG

I have a query which returns a desired output. SELECT shop, JSON_AGG(item_history.* ORDER BY created_date DESC) as data FROM item_history GROUP BY shop; Results: [ { "shop&...
0
votes
1answer
31 views

Rank calculation using variables working only sometimes

I have the following code in MariaDB that was working perfectly well yesterday. It calculates a rank and orders by the data by another column: SET @c = (SELECT COUNT(*) FROM myTable); SET @rownum = 0; ...
1
vote
3answers
48 views

Change column values but keep order in MySQL

Lets consider I have a table: CREATE TABLE student ( id int, name varchar(128) ); INSERT INTO student (id,name) VALUES (1,'John'), (2,'Bob'), (3,'Alice'), (4,'Don'), (5,'Eve'); I need the 'names' ...
1
vote
1answer
47 views

Best index for case insensitive match on one field and ordering by another field

I want to run a query like this: SELECT * FROM "core_user" WHERE UPPER("core_user"."email"::text) = UPPER('emailsearch@example.com') ORDER BY "core_user"."...
1
vote
1answer
35 views

Order a nested table with path index according to list_order in SQLite

I have a SQL table with this nested structure: CREATE TABLE items (`id` INTEGER, `item_name` VARCHAR(6), `parent_id` INTEGER, `list_order` INTEGER); My SQLite version does not support window function,...
0
votes
1answer
17 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
2answers
68 views

Strange behavior of TOP 1 clause sql server

I have a SQL Server database and restored it on another server on a different machine. Now I run following same query on both DBs which produces same below result set. select person_id, visit_id, ...
0
votes
1answer
23 views

Order by starting from "the middle" in a windowing function

What I have is a table like this, where I need to do a running total of the values column for the upcoming months. The issue is that I would like to start from month 4 (shown in column 3) and go all ...
0
votes
1answer
24 views

MySql ORDER BY slow with join, fast as 2 queries

Why is the following query slow but fast when I provide the values inline? select u.* from user u join user_group g on u.group_id = g.id where g.account_id = 1 order by u.id limit 10; -- takes ~30ms ...
0
votes
1answer
45 views

Postgresql: sort by value position in array column, then by secondary order

I'm not quite sure what the best way to phrase this is... So in my DB there is pillars text array which is basically an enum where providers ordered what values meant the most to their business, from ...
-1
votes
2answers
43 views

A quick way to ORDER all IDs randomly in MySQL [closed]

I need to assign random IDs to a table. Thus, I create a mapping table as CREATE TABLE t2 ( ID int(11) unsigned NOT NULL AUTO_INCREMENT, SourceID int(11) unsigned NOT NULL, UNIQUE INDEX(SourceID), ...
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: ...
3
votes
2answers
144 views

Order by column which casted to varchar

situation is that, i want to prepare datasets for front end chart. the raw data from table contains LAC | FLUID_MERIT|FA_BDATE |GRP |FARM_FK ------------------------------------------ 0 |234.56 ...
0
votes
1answer
69 views

MSSQL - Insert Into adding data into the middle of the table? [duplicate]

A bit of a random one here... I've got a basic table that I'm using for temporary data inserts - and for that reason, I don't need or want a unique ID - however, I'm seeing odd behaviour! (or it feels ...
5
votes
1answer
328 views

Are the results of the STRING_SPLIT() function returned in a deterministic order?

I need to split a comma delimited string, manipulate it, and then concatenate it back into a single string retaining the original order of the data (if possible). For example, take a column definition ...
0
votes
2answers
2k views

Clever way to order a json item by key in postgresql (two examples)

I need to sort a json item in a record, so here are two examples: First case create table jsontable ( jsonitem json ); Next I insert a json item: insert into jsontable(jsonitem) values ('{ "...
0
votes
1answer
35 views

Creating a table with Rank ordering in Oracle [closed]

I am trying to create a table in Oracle as follow: create table hops.t_distribution AS (select UID, CONTACT_EMAIL, PRODUCT_ID, rank() over(Partition by UID order by to_number(PRODUCT_ID)) as ...
8
votes
2answers
228 views

Sqlite: Finding the next or previous element in a table consisting of integer tuples

I have a sqlite table called tuples defined like create table tuples ( a INTEGER not null, b INTEGER not null, c INTEGER not null, d INTEGER not null, primary key (a, b, c, d) ) ...
1
vote
1answer
27 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, ...
4
votes
3answers
2k views

Case Insensitive ORDER BY clause using COLLATE

I have spent a long time looking for this, and I am getting mixed messages. In other DBMSs (tested in SQLite, Oracle, MariaDB, MSSQL) I can override the default sort order using the COLLATE clause: ...
0
votes
3answers
139 views

Sort varchar by its numeric fields

I'm sure this has been asked before, but since the question can be formulated in many different ways it's difficult to find the proper answer. I have an orders table with a varchar field for the order ...
1
vote
2answers
43 views

Mysql: Query which orders by default and rank (pin/hold up entries?)

I have a products table which contains all my products. Those products table gets filled permanently with new products. However, I want to have the possibility to "hold up"/"pin" ...
0
votes
0answers
15 views

Choose the index for default sort in MySQL [duplicate]

I have a table in MySQL with 3 columns a, b, c. Each has an index but none is primary key. When I run query SELECT * FROM table LIMIT 50; I am trying to have the result sorted by column b ascending, ...
1
vote
1answer
180 views

Select Query slow for Non-clustered index

I am using SQL Server 2008 R2. Select query is slow when non cluster index is used in the table compared to table without index. Query Used: select * from table order by customer_name It is too ...
0
votes
1answer
25 views

PostgreSQL: Performance: ORDER BY an indexed column is slowing down the query performance

SELECT s.id, s.name, s.updated_at, COUNT(*) OVER () FROM screens s INNER JOIN theatres t ON s.theatre_id = t.id LEFT JOIN company_screen_mappings AS csm ON csm.screen_id = s.id AND csm.is_deleted = ...
1
vote
2answers
61 views

Find the greatest difference between each unique record with different timestamps

I have a PostgreSQL table with the following schema and data: CREATE TABLE IF NOT EXISTS T( id uuid PRIMARY KEY, username varchar(15), person varchar(10), tweets int, ...
0
votes
1answer
37 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
1answer
184 views

Is there any way in Postgres to parameterise a procedure for sort order column, when the columns are of different types?

I am trying to write a function which does (some complex stuff) and returns the results in different orders based on a parameter. A simplified version would look something like this: CREATE OR REPLACE ...
0
votes
1answer
66 views

Mysql query group by to be combined with order by desc

I have the following query as below. SELECT Day(vehicleTrip.startDateTime) As day, Month(vehicleTrip.startDateTime) As month, Sum(TIMESTAMPDIFF(SECOND, startDateTime, endDateTime )) As ...
0
votes
1answer
233 views

Order by certain enum values first

I've got a table like: ╔═══╦════════════════════╦═════════════╗ ║ID ║ type (enum) ║ updated_at ║ ╠═══╬════════════════════╬═════════════╣ ║ 1 ║ friend_request ║ <date_time> ║ ║ 2 ║ ...
-1
votes
1answer
37 views

Explicitly specify sort order for MySQL query (use an "IN" list)

With a query like the following, the default will return in order of id of least to greatest even if the IN list is not in that order. In my situation the list will be supplied from some c# code. How ...
0
votes
0answers
114 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 ...
2
votes
2answers
29 views

How to order some rows one way, and the rest another way?

I am trying to sort rows from a table in PostgreSQL 10.12: CREATE TABLE example ( id serial PRIMARY KEY, val text NOT NULL, x integer NOT NULL, y integer NOT NULL ); INSERT INTO example (...
3
votes
2answers
230 views

Results of using a comparison operator in the ORDER BY clause

There is a table x: select * from x; +------+------+ | a | b | +------+------+ | aaa | 999 | | bbb | 888 | | ccc | 777 | +------+------+ What do the following queries do? select * from x ...
0
votes
3answers
48 views

What is wrong with this syntax of a multi-table DELETE...ORDER BY?

I have the following query: DELETE proxies, pxpl FROM proxies JOIN proxy_xref_proxy_list AS pxpl ON proxies.ID=pxpl.ProxyID WHERE pxpl.DeactivationTS < (NOW() - INTERVAL 24 HOUR) ORDER BY pxpl....
1
vote
2answers
88 views

SQL Server order of execution and query plan

I am aware that the order of execution of a query in SQL Server is (excluding TOP, etc): FROM, JOIN WHERE GROUP BY HAVING SELECT ORDER BY However, I cannot understand how in the query plan the sort (...
1
vote
2answers
32 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 '...
8
votes
2answers
4k views

SELECT DISTINCT ON, ordered by another column

Please consider the following table test: CREATE TABLE test(col1 int, col2 varchar, col3 date); INSERT INTO test VALUES (1,'abc','2015-09-10') , (1,'abc','2015-09-11') , (2,'xyz','2015-09-12') , (2,...
0
votes
1answer
24 views

Select value in Left Join ordered by Date

I am trying to LEFT JOIN a value to sort by the most recent date, but for some reason it is only returning a result for the last returned result. These are my table layouts cs_users +---------+-----...
0
votes
1answer
561 views

Pagination while sorting a non unique text column

What are the ways to paginate a table on a non unique text column? On a unique timestamp column, one can create an index on it and execute the following to move forward SELECT * FROM users WHERE ...
0
votes
2answers
65 views

How to sort aggregate function call

If I have an expensive function call that takes a JSONB and returns an INT, e.g. CREATE FUNCTION my_expensive_function(data JSONB) RETURNS INTEGER AS How do I go about ordering the aggregate result ...
0
votes
1answer
1k views

ORDER BY items must appear in the select list if SELECT DISTINCT is specified

SELECT DISTINCT * FROM ( SELECT a.Title, a.ID FROM TableA a LEFT JOIN TableB b ON a.ID = b.XID) c ORDER BY CASE WHEN c.ID > 10 THEN c.Title ELSE c....
0
votes
2answers
203 views

How to save working hours of multiple stores and then query it without duplication?

I'm want to create database with stores. Each store has their own working hours in a week. Then I want to query the store and sort them by nearest store which is currently open. So far I create this ...
0
votes
2answers
234 views

Does the 'ORDER BY' clause of a subquery pass through to the main results (POSTGRES)

I've got a query written something like this..... with subquery as ( SELECT A, B, C, D, row_number() OVER(PARTITION BY X ORDER BY Y) as RN FROM blah blah ORDER BY A,B LIMIT 100 ) SELECT C,...

1
2 3 4 5
8