Questions tagged [order-by]
A clause used in SQL SELECT statements to sort query results.
457 questions
0
votes
2
answers
31
views
Ordering by IN clause order
Suppose I want the order of output records match the order in the IN clause
SELECT * FROM (
SELECT 1 AS id, 'one' AS text FROM DUAL
UNION ALL
SELECT 2 AS id, 'two' AS text FROM DUAL
) t WHERE t.id IN (...
- 169
1
vote
1
answer
39
views
MySQL order by with conditional clauses
I have these 2 tables
CREATE TABLE `users` (
`id` bigint NOT NULL AUTO_INCREMENT,
`status` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=330031656 DEFAULT CHARSET=...
- 203
1
vote
2
answers
98
views
How can I do arithmetic in the ORDER BY clause with a column aliased in my SELECT clause?
The following is illegal.
SELECT
schema_id + 2 AS evil_column
FROM
sys.tables
ORDER BY
evil_column + 3;
If you remove the + 3, then it is legal.
Assuming that I want to do arithmetic with ...
- 801
1
vote
1
answer
39
views
Why does ORDER BY significantly slow down my query with a computed score of trigram similarity and null field?
I'm working on optimizing a query in PostgreSQL, and I've encountered a performance issue when using the ORDER BY clause. The query is intended to search profiles based on a similarity match to a name ...
1
vote
1
answer
65
views
mysql ORDER BY return order differently
Trying to figure out why mysql's ORDER BY clause can return order differently, e.g.:
The two files are both generated by using mysql on command line with a single command:
mysql mydb -e 'SELECT ...
- 143
-2
votes
2
answers
70
views
Sort By Child Table But Maintain Parent Table Order
I am currently trying to sort the results of two tables in such a way that the primary sort field is one in the child table but would like to keep the parent table results from being moved out of ...
- 222
0
votes
1
answer
37
views
Sort by maximum value per partition [closed]
I have a table with data something like below:
| ID(pk) | name | count |
|--------|------|-------|
| id1 | xyz | 98 |
| id2 | ptr | 5 |
| id3 | xyz | 2 |
| id4 | de | 1 ...
- 103
2
votes
1
answer
273
views
Emulate Loose Index Scan for multiple columns with alternating sort direction
A while back I asked this question about efficiently selecting unique permutations of columns in Postgres. Now I have a follow-up question regarding how to do so, with the addition of being able to ...
- 197
0
votes
2
answers
112
views
Trying to renumber a field in order grouping by another
So I have a table that has a column labeled 'sequence', it is not in fact a sequence data type, just a numeric field that translates in the object model to show items in a certain order. In a handful ...
0
votes
2
answers
220
views
Is default ordering affected by clustered index? [duplicate]
My colleague and I are debating whether the default ordering of rows from a SELECT statement (without specifying any ORDER BY) is controlled by the clustered index or not.
He demoed this by creating a ...
- 151
1
vote
1
answer
54
views
Identify if the sequence of visits to an area id was interrupted at any point
I have a PostgreSQL table df with the following information:
area_id
trip_nr
timestamp
a
3
29/06/2022 17:18:03
a
2
29/06/2022 17:18:02
a
1
29/06/2022 17:18:01
b
1
28/06/2022 19:21:03
c
3
28/06/...
- 35
3
votes
2
answers
495
views
Managing the order of aggregation in Postgres
Suppose I have the following merging function that merges two jsonb values overwriting duplicate keys with the values from the second
create or replace function jsonb_concat(a jsonb, b jsonb) returns ...
- 161
0
votes
1
answer
53
views
ORDER BY slows query performance
I see very bad query performance when ORDER BY is added
act.activity_schedule is a DB view. There are indexes on date and start_hour.
create index activity_schedule_tab_date_index
on ...
- 141
0
votes
1
answer
50
views
Force MySQL to use specific index on ORDER BY clause with LIMIT to satisfy join
MySQL 8.0.30
I have three tables defined:
C
CT
T
CT is a linking table (many-to-many between C & T)
The query I'm trying to optimize is of the following kind:
SELECT C.*, T.*
FROM C JOIN CT ON C....
- 1
1
vote
2
answers
125
views
Explain plan will sort the result after join even the column included in index
I am using SQL Server 2022 Developer
Trying get all AccessLog that classified to type 1.
SELECT [t].[Time], [u].[UserName], [t].[Type], [t].[Message]
FROM [AccessLog] AS [t]
LEFT JOIN [...
- 11
1
vote
2
answers
1k
views
Why is Postgres so slow to order those 200 rows that are already ordered?
I have two relatively complex SQL queries which I join using a UNION ALL. Each individual query is fast and returns instantly. The problem is that once joined together they perform terribly bad, and ...
- 191
0
votes
1
answer
58
views
Oracle MAX gives different result than ORDER BY desc
When trying to sort some hexadecimal values I've got a different results for MAX() aggregation and ORDER BY desc sorting:
with some_data as
(
select '123A55' as hex
from dual
union all
...
- 110
1
vote
1
answer
24
views
How can I sort by "release" in Mysql, where e.g. 10.10 comes after 10.9, with non-numerical entries both before and after?
If I have a table as per https://www.db-fiddle.com/f/arPEdUty3U6AJEQfqYEyRc/1 with a list of releases:
INSERT INTO `releases` (`ReleaseID`, `ReleaseNumber`) VALUES
(1, '10.6'),
(2, '10.8'),
(3, '_TBD')...
- 173
0
votes
0
answers
156
views
How can I optimize the view in MariaDB for Order By?
I have a list of products. Each has many tags. Those tags have normal tables, but they also have OLAP tables that present CSV for each product.
For example, a chair can have 'furniture', 'wood', and '...
- 1,505
-2
votes
2
answers
561
views
In Snowflake, or SQL Server, how do I order results in the same order as my query?
In Snowflake, I'm querying the Information_Schema for the number of columns in each of a series of listed tables. How do I write my query so that Snowflake will return the results in the same order ...
- 65
0
votes
1
answer
707
views
Can you make Postgres execute ORDER BY after OFFSET and LIMIT?
Consider this pagination scenario
id
name
last_name
1
Mickey
Mouse
2
Donald
Duck
3
Scrooge
McDuck
4
Minerva
Mouse
5
Goofus
Dawg
6
Daisy
Duck
SELECT * FROM users
ORDER BY id DESC
LIMIT 3;
The ...
- 169
0
votes
1
answer
44
views
How to query in the given order
select * from table where options=(10,223,43,1,23,54,323,32)
But the result is not coming in the given order of options. Options need to be changed frequently.
- 1
1
vote
2
answers
73
views
MySQL ORDER BY Indexes When ORDER BY May be Unknown
For some background lets say I have two tables:
create table orders (
id int unsigned auto_increment primary key,
uuid char(36) not null,
status_id int unsigned not null,
account_id int unsigned ...
- 11
0
votes
1
answer
85
views
Is it possible to sort rows by values from generate_series()?
I multiply result rows of a SELECT with CROSS JOIN generate_series(1, max). My query is like:
select id, name
from person
CROSS JOIN generate_series(1, 4)
where <condition>;
I have this result:
...
0
votes
1
answer
665
views
Why does my LIMITed SELECT fetch the last record of the table, not the first?
I read that to fetch the first table record, you may use LIMIT 1. I tried to apply that knowledge to my JOIN statement and got a diametrically opposite result. It joined the last record of the joined ...
- 169
0
votes
1
answer
104
views
How can I get a valid rank counter?
I'm making a raking system. I want get the line in one request.
Here is a DB fiddle of my complete example: https://www.db-fiddle.com/f/3Com2wnrhaqfiTKqJSZtft/0
In the example, the UUID used is ...
- 181
2
votes
1
answer
224
views
How can I paginate when ordering by `date_bin`?
I have the following query
SELECT u.update_time, about_me
FROM users u
ORDER BY date_bin('14 days', u.update_time, '2023-04-07 23:11:56.471560Z') DESC, LENGTH(u.about_me) DESC, u.user_id;
I get the ...
- 145
1
vote
1
answer
113
views
What is the use of the order by ASC keyword in an SQL query?
As most, if not all, implementation of SQL use the ASC keyword by default in an ORDER BY clause when it is omitted.
Are there valid use case for the ASC keyword?
I am NOT asking for psychological ...
- 119
2
votes
1
answer
113
views
MySQL - multiple CASE ordering with joined table
I have two tables:
EVENTS
ID
Name
1
First event
2
Second event
3
Third event
4
Fourth event
5
Fifth event
EVENTS_META
ID
EventID
MetaKey
MetaValue
1
2
date_expired
2023-02-09 00:00:00
2
2
...
- 23
0
votes
2
answers
63
views
Indexing for multiple ORDER BY in MySQL 8
I feel like I have optimized this query to the best of my ability, but it is still slow (usually takes 0.5s - 2s in development).
It seems like the ORDER BY is the culprit, as when I get rid of it, it ...
- 3
0
votes
0
answers
890
views
MySQL 8 taking 1000x times longer to execute this query vs 5.7 when ORDER BY is used
This query comes from an ORM (Laravel Eloquent) on a WordPress database. I upgraded to MySQL 8 from 5.7 (AWS RDS), and the performance went from pretty speedy to basically broken. API requests that ...
- 1
4
votes
2
answers
516
views
"ORDER BY column=value" using indexes (no full table scan)
I'm trying to hack a FLOSS application called Phabricator / Phorge
Let's take this simple MySQL table storing some questions by ID and their status (open, closed, invalid etc.):
CREATE TABLE `...
- 149
0
votes
2
answers
1k
views
Sort by multiple columns, using nulls first on one of them
Given a members table, having the following columns:
accepted_at datetime
, updated_at datetime NOT NULL
Given this query:
select * from members
order by accepted_at DESC NULLS FIRST, updated_at ...
- 179
0
votes
0
answers
58
views
Select columns from joined tables, group or distinct by a single column, and order by another column
Here's essentially what I'm trying to do:
SELECT
deals.*,
retailers.title AS retailer_title,
ST_Distance(retailers.lonlat, 'SRID=4326;POINT(-118.4104684 34.1030032)' :: geography, 15 * ...
1
vote
0
answers
274
views
Postgres sorted join with millions of rows, low cardinality, slow query - time to partition tables?
I have an API that is used to query for data about programs and other forms of content.
We have a few tables that I need to join on to fullfil an ad-hoc query, and I am running into trouble speeding ...
- 11
-1
votes
2
answers
1k
views
XML data type cannot be compared or sorted
My source is in SQL server; my target is in Snowflake. The target table was created by extracting the XML column only from the source. I need to compare the first 20 rows in source and target to see ...
- 65
0
votes
1
answer
42
views
how to build a multi-column index for sorting, where one of the columns may be null
I'm using postgresql 11, and am looking for a way to optimize the following query, to retrieve the orders by "most recently handled", i.e. most recently completed or created first, where ...
- 101
1
vote
1
answer
287
views
Column-wise UNION, or ordering two columns of SELECT/JOIN query with distinct orderings
I have a table, employee, and one of the columns is last_name, which stores strings.
Using a SELECT query, I am trying to get the last_name column, ordered alphabetically, as one column, and another ...
- 111
0
votes
0
answers
196
views
How can I speed up INSERT INTO ... SELECT ... ORDER BY queries?
I recently learned that it is advisable to have deterministic queries when using a replication instance. Therefore, we started adding ORDER BY to our INSERT INTO (...) SELECT (...) FROM X statements. ...
- 111
0
votes
1
answer
47
views
How to order strings in BigQuery to have lowercased characters ordered before uppercased characters?
I'm updating a reporting tool implementation so that it queries BigQuery instead of PostgreSQL. The PostgreSQL data is ingested in BigQuery. One requirement is for the report results to stay exactly ...
- 349
1
vote
1
answer
78
views
Order a row before/after another based on value in the previous row
I've been trying to figure out a way to order a row after another based on the value of a column from the previous row.
In my SQL query below, I first initially want to order the dataset by distance ...
- 11
0
votes
3
answers
232
views
Does Oracle guarantee final output row order when it is set in the inline view
This query is based on 2 tables where some rows in PerfData1 have multiple child records. Rows are not duplicate but some ids are. I need to take only 1 of such rows and this is also a paging query, ...
- 216
4
votes
5
answers
5k
views
How to update table records in reverse order?
I've a table Student
Id Name Mark
1 Medi 10
2 Ibra 15
3 Simo 20
and I want to update it, where I want to reverse it in descending order only Name and Mark and keep Id in its order:
...
- 292
0
votes
0
answers
22
views
Apply comparision within group of rows to get one row per group [duplicate]
I have a table ordered by fruit and date resulting from a join such as the following:
+--------+------------+
| fruit | date |
+--------+------------+
| apple | 2002-02-02 |
| apple | 2003-03-...
- 103
12
votes
3
answers
12k
views
Are results from UNION ALL clauses always appended in order?
As per standard SQL UNION / UNION ALL do not guarantee any particular sort order without an outer ORDER BY clause - like there is hardly any place in SQL where sort order is guaranteed without ORDER ...
- 182k
7
votes
4
answers
463
views
Relationships between like matching and comparison
Naively I assumed that the relationship between a like 'foo%' and a >= 'foo' is that the latter matches the former and some additional rows that come after in the index order. More generally, I ...
- 775
0
votes
0
answers
82
views
Rewrite a query to sort data in sql server
I have a stored procedure that uses two main tables and some other tables to get the required customer data. One of the parameters passed into the SP contains an xml with customerids that are shredded ...
- 601
1
vote
1
answer
114
views
Get first id of the latest 25000 entries?
I have a database with 200000 rows. Of those 200000 rows I want the latest 25000 and from those I need the oldest id (column entity_id).
This is my query so far: select * from sales_flat_order order ...
- 113
4
votes
1
answer
2k
views
Postgres order by with multiple columns
So imagine I have a result set that looks something like this:
+----+------------+------------+
| Id | Date_One | Date_Two |
+----+------------+------------+
| 1 | 2022-05-12 | null |
| 2 |...
- 153
0
votes
0
answers
414
views
Order by string with numbers
I need to order the query using a pattern that mixes text and numbers. This is the default ORDER BY which is "correct" if not considering the entire numbers. But as you can see, at the end ...
- 21