Questions tagged [order-by]
A clause used in SQL SELECT statements to sort query results.
386
questions
0
votes
1answer
21 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
15 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
15 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
39 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
42 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
139 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
41 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
160 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
195 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
34 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
220 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
26 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
838 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
45 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
34 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
82 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
20 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
59 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
28 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
61 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
67 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
34 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
89 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
28 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
209 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
35 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
72 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
31 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 '...
7
votes
2answers
2k 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
224 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
47 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
712 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
71 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
44 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,...
0
votes
1answer
1k views
How to reorder rows in Postgresql
I've got the following table
And I have to change the order of the rows in an unpredictable way for example:
Set the position of the row with id = 1 after the row with id = 3 and preserve the ...
0
votes
1answer
36 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
1answer
114 views
Sybase ASE 15.7 sort ordering
I have my sort ordering in Sybase as:
45 190 altdict - Alternate (lower-case first) dictionary ordering
Am I right that alternate means "aAbBcC..zZ" ordering?
What should I do to change ordering ...
0
votes
2answers
29 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-...
1
vote
1answer
81 views
Determine Order Streak
For a particular customer, I have data returned by a query that looks like this:
OrderDate OrderCount
2020-01-12 0
2020-01-13 17
2020-01-14 22
2020-01-15 21
2020-01-16 30
2020-01-17 12
2020-...
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....
1
vote
1answer
102 views
Is SELECT FOR … UPDATE *necessary* to DELETE or UPDATE multiple rows without deadlock?
Consider the following when the transaction isolation level is Read Committed (the default):
UPDATE example
SET foo = 'bar'
WHERE ...
and
DELETE FROM example
WHERE ...
Neither DELETE nor UPDATE ...
0
votes
1answer
64 views
ORDER from 2 tables to achieve a specific order returned by an API
There's an external API that returns image similarities, based on an "image ID" and a "image version" (a same image can have multiple versions).
The similarity ORDER is given by the API.
The API ...
0
votes
2answers
23 views
Can I tell postgres to use a different “collation” for an integer column?
Let's say I'm using an integer column to represent an "enum" in my application code, for a "priority" attribute.
0=low
1=medium
2=high
The app is operating for a while like that, and then I want to ...
6
votes
1answer
295 views
Why does ORDER BY NULLS LAST affect the query plan on a primary key?
Using PostgreSQL 11, I have the following table with around 450 million rows:
postgres=> \d+ sales
Table "public.sales"
...
0
votes
1answer
125 views
Ordering by field, constructed by subquery is slow
My tables (oversimplified for clarity):
documents: registrations:
|id| |id|document_id|date|
My simplified query:
SELECT *
FROM (SELECT documents.*,
(SELECT max(date)
...
5
votes
1answer
231 views
MySQL uses INDEX for optimizing ORDER BY with GROUP BY except when you add a JOIN
Question
Why can't Query #2 use the same (car_trims.horsepower_peak) index to optimize the sorting of the rows as Query #1? The only difference between the two queries is the addition of the JOIN in ...
1
vote
1answer
735 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 ...
