Questions tagged [order-by]

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

Filter by
Sorted by
Tagged with
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 ...
laurent's user avatar
  • 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 ...
Andy DB Analyst's user avatar
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')...
Ben Holness's user avatar
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 '...
Saeed Neamati's user avatar
-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 ...
Jimbo's user avatar
  • 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 ...
Sergey Zolotarev's user avatar
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.
sh3hz's user avatar
  • 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 ...
jhdba113's user avatar
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: ...
nanocellule's user avatar
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 ...
Sergey Zolotarev's user avatar
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 ...
Elikill58's user avatar
  • 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 ...
DanMossa's user avatar
  • 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 ...
Stefmachine's user avatar
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 ...
Martin J's user avatar
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 ...
Luck3R's user avatar
  • 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 ...
Will's user avatar
  • 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 `...
Valerio Bozz's user avatar
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 ...
Ben's user avatar
  • 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 * ...
JohnDoe1999's user avatar
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 ...
flanders9er's user avatar
-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 ...
Jimbo's user avatar
  • 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 &...
Anrysys's user avatar
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 ...
ChuckE's user avatar
  • 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 ...
m. lekk's user avatar
  • 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. ...
binford's user avatar
  • 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 ...
Florent2's user avatar
  • 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 ...
ordane87's user avatar
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, ...
T.S.'s user avatar
  • 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: ...
XM01 - stands with Palestine's user avatar
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-...
loris's user avatar
  • 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 ...
Erwin Brandstetter's user avatar
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 ...
John's user avatar
  • 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 ...
lifeisajourney's user avatar
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 ...
user4095519's user avatar
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 |...
Robin Jonsson's user avatar
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 ...
keriuz's user avatar
  • 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 ...
Spinettaro's user avatar
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 ...
Matteo Tassinari's user avatar
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', ...
Sayaman's user avatar
  • 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....
Underwater_developer's user avatar
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 (&...
user17987204's user avatar
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 ...
HeyJude's user avatar
  • 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 ...
Milen Mihalev's user avatar
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. ...
Santiago Correa's user avatar
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 ...
Trevor Wood's user avatar
-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. ...
grochu's user avatar
  • 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 ...
Dolphin's user avatar
  • 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 ...
Doug Coats's user avatar
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 ...
Héctor's user avatar
  • 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 ...
D M's user avatar
  • 21

1
2 3 4 5
9