Questions tagged [order-by]
A clause used in SQL SELECT statements to sort query results.
409
questions
-1
votes
0
answers
27
views
Perhaps I've made a mistake about "ORDER BY" statement but I can't figure out
I want to show the summary order by the quantity of each group ascendingly then alphabetically but it's not working as expected. 🤔
(
SELECT
CONCAT(NAME, '(', LEFT(OCCUPATION, 1), ')')
...
- 1
1
vote
0
answers
28
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 ...
- 135
0
votes
1
answer
36
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', ...
- 11
1
vote
1
answer
39
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
29
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 (&...
1
vote
3
answers
259
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 ...
- 331
0
votes
2
answers
126
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
150
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
602
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
95
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
110
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 ...
- 477
5
votes
1
answer
177
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
84
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 ...
- 181
0
votes
1
answer
30
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
0
votes
2
answers
46
views
Sort index doesn't work for MySQL
I have a table with more than a million records. I get the data by using LIMIT, but also ORDER BY. The latter causes a huge performance problem.
My query ends with this:
... ORDER BY `record`.`...
- 111
4
votes
1
answer
85
views
Ordering rows by repeating pattern
Given a table in Postgres like
CREATE TABLE t1 (
type text NOT NULL,
value int NOT NULL
)
where type could be A, B or C and given an arbitrary, repeating pattern like
A B B C B B
how can I query ...
- 163
2
votes
3
answers
71
views
Ordering streets by their names and polysyllabic numbers in SQL query
In Oracle DB there is a table with some data:
LAG
HNR
STREET
00020
44
Aachener Straße
00020
44/2
Aachener Straße
00020
44/1
Aachener Straße
00020
46
Aachener Straße
00020
46/6
Aachener Straße
...
- 167
0
votes
1
answer
247
views
How to use ORDER BY and LIMIT inside JSON_AGG
I have a query which returns a desired output.
SELECT
shop,
JSON_AGG(item_history.* ORDER BY created_date DESC) as data
FROM item_history
GROUP BY
shop;
Results:
[
{
"shop&...
- 3
0
votes
1
answer
66
views
Rank calculation using variables working only sometimes
I have the following code in MariaDB that was working perfectly well yesterday. It calculates a rank and orders by the data by another column:
SET @c = (SELECT COUNT(*) FROM myTable);
SET @rownum = 0;
...
- 101
1
vote
3
answers
62
views
Change column values but keep order in MySQL
Lets consider I have a table:
CREATE TABLE student (
id int,
name varchar(128)
);
INSERT INTO student (id,name) VALUES
(1,'John'),
(2,'Bob'),
(3,'Alice'),
(4,'Don'),
(5,'Eve');
I need the 'names' ...
1
vote
1
answer
57
views
Best index for case insensitive match on one field and ordering by another field
I want to run a query like this:
SELECT *
FROM "core_user"
WHERE UPPER("core_user"."email"::text) = UPPER('emailsearch@example.com')
ORDER BY "core_user"."...
- 113
1
vote
1
answer
76
views
Order a nested table with path index according to list_order in SQLite
I have a SQL table with this nested structure:
CREATE TABLE items (`id` INTEGER, `item_name` VARCHAR(6), `parent_id` INTEGER, `list_order` INTEGER);
My SQLite version does not support window function,...
- 113
0
votes
1
answer
20
views
Why is below 2 query generating different execution plan in MYSQL?
Only difference in the below query is the order by column. The resultset of both query is same.
However, the 2nd query execution plan shows 'Using temporary; Using filesort' for ordering where as for ...
- 1
0
votes
2
answers
71
views
Strange behavior of TOP 1 clause sql server
I have a SQL Server database and restored it on another server on a different machine. Now I run following same query on both DBs which produces same below result set.
select person_id, visit_id, ...
- 75
0
votes
1
answer
34
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 ...
- 249
0
votes
1
answer
81
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
...
- 113
0
votes
1
answer
136
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 ...
- 245
-1
votes
2
answers
57
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),
...
- 4,179
1
vote
0
answers
51
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:
...
- 111
3
votes
2
answers
147
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 ...
- 135
0
votes
1
answer
134
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 ...
- 1
5
votes
1
answer
595
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 ...
- 24.2k
0
votes
2
answers
4k
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 ('{ "...
- 13
0
votes
1
answer
40
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 ...
- 1
8
votes
2
answers
263
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
1
answer
32
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,
...
- 201
4
votes
3
answers
4k
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:
...
- 2,337
0
votes
3
answers
393
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
2
answers
57
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" ...
- 113
1
vote
1
answer
422
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 ...
- 3,055
0
votes
1
answer
50
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 = ...
- 131
1
vote
2
answers
64
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,
...
- 13
0
votes
1
answer
50
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 ...
- 1
0
votes
1
answer
529
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 ...
- 59
0
votes
1
answer
78
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 ...
- 177
1
vote
1
answer
1k
views
Order by certain enum values first
I've got a table like:
╔═══╦════════════════════╦═════════════╗
║ID ║ type (enum) ║ updated_at ║
╠═══╬════════════════════╬═════════════╣
║ 1 ║ friend_request ║ <date_time> ║
║ 2 ║ ...
- 369
-1
votes
1
answer
60
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 ...
- 113
0
votes
0
answers
163
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 ...
- 21
2
votes
2
answers
43
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 (...
- 125
3
votes
2
answers
288
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 ...
- 133