Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [order-by]

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

Filter by
Sorted by
Tagged with
2 votes
1 answer
103 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 ...
hunter's user avatar
  • 197
0 votes
2 answers
49 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 ...
Jonathan Beck's user avatar
0 votes
2 answers
116 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 ...
user1589188's user avatar
1 vote
1 answer
46 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/...
Ruan's user avatar
  • 35
3 votes
2 answers
176 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 ...
Dragas's user avatar
  • 151
0 votes
1 answer
43 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 ...
Robert's user avatar
  • 141
0 votes
1 answer
35 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....
ktn's user avatar
  • 1
1 vote
2 answers
75 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 [...
Uni's user avatar
  • 11
1 vote
2 answers
263 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
  • 191
0 votes
1 answer
37 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
23 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
101 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
217 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
408 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
43 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
62 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
62 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
535 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
90 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
  • 221
2 votes
1 answer
179 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
101 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
88 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
47 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
741 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
426 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
2 answers
890 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
  • 179
0 votes
0 answers
36 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
233 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
771 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
1 answer
39 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
223 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
165 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
38 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
  • 349
1 vote
1 answer
61 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
165 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
  • 216
4 votes
5 answers
4k 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: ...
XMehdi01's user avatar
  • 302
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
10 votes
2 answers
8k 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
438 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
  • 765
0 votes
0 answers
77 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
72 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
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 |...
Robin Jonsson's user avatar
0 votes
0 answers
367 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
175 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
670 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
351 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
  • 211
1 vote
1 answer
564 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
118 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
5k 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
  • 447
0 votes
2 answers
786 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

1
2 3 4 5
9