Questions tagged [order-by]
A clause used in SQL SELECT statements to sort query results.
443
questions
1
vote
2
answers
65
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 ...
- 177
0
votes
1
answer
31
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
...
1
vote
1
answer
18
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
56
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,417
-2
votes
2
answers
90
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
180
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 ...
- 157
0
votes
1
answer
39
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
52
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
49
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
327
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 ...
- 157
0
votes
1
answer
73
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 ...
- 205
2
votes
1
answer
155
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
84
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
62
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
41
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
594
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
310
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 `...
- 145
0
votes
1
answer
596
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 ...
- 169
0
votes
0
answers
34
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
195
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
464
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
0
answers
206
views
Sorting not working in mysql stored procedure
The procedure works, but sorting of dynamic columns does not work, according to their data type (each column has its own data type: CHAR, SIGNED, DECIMAL...). Column names are created dynamically &...
- 1
0
votes
1
answer
38
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
177
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
137
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
31
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 ...
- 339
1
vote
1
answer
49
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
123
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, ...
- 206
4
votes
5
answers
3k
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:
...
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
8
votes
2
answers
6k
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 ...
- 172k
7
votes
4
answers
420
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 ...
- 755
0
votes
0
answers
75
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 ...
- 437
1
vote
1
answer
42
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
3
votes
1
answer
1k
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 |...
- 143
0
votes
0
answers
321
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
0
votes
0
answers
123
views
PostgresSQL pagination, tuning indexes and order by
I have a table with those columns: id, uuid, customer_id, building, dept, values
I have a combined index on those columns: building, dept.
The table contains ~80 millions of rows
I would like to apply ...
- 121
1
vote
0
answers
624
views
Very slow MariaDB query when adding ORDER BY/LIMIT but there are no rows
I am facing an issue with MariaDB 10.1 on a log table with the following (simplified) structure:
CREATE TABLE logs (
log_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
log_date TIMESTAMP DEFAULT ...
- 167
0
votes
1
answer
281
views
SQL query ORDER BY gives different result every once in a while (5 second interval)
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
INNER JOIN wp_product dg ON dg.post_id = wp_posts.ID
AND ( dg.location IN ('XV', ...
- 201
1
vote
1
answer
496
views
CTE clause not able to be used in final ORDER BY statement
Trying to order by distance using an input geometry t(x), while comparing to geometries from a JOINed table.
WITH "nearest_groups" as (
SELECT groups.id, ST_Distance(t.x, locations....
0
votes
0
answers
94
views
How can I ORDER BY the "most relevant" entries in this context?
I have this query:
SELECT * FROM "my bookkeeping" WHERE
("timestamp" > $1::date - INTERVAL '7 days' AND "timestamp" < $1::date + INTERVAL '7 days')
AND (&...
3
votes
3
answers
4k
views
How does order by zero work
In the following script (see a full demo here):
SELECT *
FROM Table1
ORDER BY
CASE WHEN Field1 IS NOT NULL
THEN 0
END
What is the type of the 0?
How 0 is interpreted? It's not the ...
- 437
0
votes
2
answers
674
views
MySQL multiple subqueries - slow ordering
Any idea how to optimise this query?
It runs very slow when using the ORDER BY (~10 seconds) clause. If I remove the order_by clause, then it executes for milliseconds.
Problem is, that I need to been ...
0
votes
1
answer
1k
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.
...
2
votes
3
answers
1k
views
Order large numbers that are saved as a varchar
An unsigned bigint is the maximum int size in mysql as far as I'm aware. So for good measure I'm using varchar(255) since some values in my db are larger than an unsigned bigint.
I'm looking to order ...
- 127
-2
votes
1
answer
667
views
What is the best way to order Partitioned table?
What is the most efficient way to get ordered data from a partitioned table?
I have this query:
SELECT TOP (4000)
*
FROM dbo.MS
ORDER BY MANDT
, MBLNR
, MJAHR
, ZEILE
Table is partitioned by MBLNR.
...
- 45
1
vote
3
answers
4k
views
Is it possible to sort by the sequence of input IDs?
I want to find a user's favorite music list. First step, I query all id from user's favorite table. In the next step I query the music list by id from songs table. But now I want to sort rows by the ...
- 755
5
votes
1
answer
430
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 ...
- 349
3
votes
1
answer
114
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 ...
- 287
0
votes
1
answer
49
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 ...
- 21