Questions tagged [sorting]
The sorting tag has no usage guidance.
142 questions
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
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 ...
11
votes
6
answers
3k
views
Is it bad practice to have a "most_recent" boolean column in addition to a "create_at" timestamp column to track the latest version of a record?
The table looks like this, it's SCD type 2:
+-----------+------------------+------------------------+
| id (text) | version (serial) | created_at (timestamp) |
+-----------+------------------+---------...
- 213
0
votes
0
answers
47
views
MongoDB huge amount of temp files
I have a MongoDB replica set. The primary one creates a huge amount of temp files with the name extsort-sort-executor in every seconds, which generates more than 80Gb of temp files under 10 minutes...
...
0
votes
1
answer
241
views
MongoDB huge amount of temp files
I have a MongoDB replica set. The primary one creates a huge amount of temp files with the name extsort-sort-executor in every seconds, which generates more than 80Gb of temp files under 10 minutes...
...
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
0
votes
2
answers
74
views
how sorting nullable number and ones is last
my list something like this
Id
Value
ِDate
1
null
2020
2
0
2022
3
1
2021
4
1
2023
5
100
2024
2
250
2018
I want to sort by column [Value] so that ones are at the end
The result should be as ...
- 9
0
votes
2
answers
109
views
The effectiveness of compound index where the prefix is continuous / high cardinality
schema:
{
time_utc: "milliseconds",
city: "string",
age: "integer"
}
index:
{
time_utc: 1,
city: 1,
age: 1
}
sample query:
col.aggregate([
{
$match: { ...
1
vote
2
answers
150
views
Why can SQL Server not perform a TOP N SORT between an Index Seek and the Key Lookup?
I am looking into a minor performance issue, where an optimizer tool is basically saying, "hey just go ahead and include all the columns on this table in this index" which is a horrible ...
- 13
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
1
answer
2k
views
How to sort by joined table property in PostgreSQL?
I am new to SQL, and am using an ORM for the most part (well, a SQL builder, not raw SQL). I have a simple dictionary app in Vercel Postgres version 15 with this sort of schema:
CREATE TABLE words (
...
- 221
0
votes
1
answer
50
views
How to get only last relation sorted by datetime
I have users and their payments (ok/fail) and need to select all users that have last payment failed (by date).
Mariadb 10+.
That's schema and how i've tried to solve this, but definitely wrong: http:/...
- 103
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
1
vote
1
answer
64
views
Getting ID when sorting by joined table using group also
I have small query to getting products based on variants (colors, materials), like:
SELECT
ANY_VALUE(id) AS id,
parent_id,
COUNT(id),
color,
material,
MIN(price) AS priceMin
FROM
(
...
- 123
4
votes
2
answers
767
views
Numeric collation sorts by digits instead of value- postgres
I have a table of users with ids in the following form [email protected].
When searching and sorting the users, I need [email protected] to be before [email protected] but since 4 is “smaller” than @ ...
- 41
2
votes
1
answer
58
views
Identify unique trips for each user where walking is followed by trip start
I have a table trips with the following information
Row
User
Timestamp
Event
1
1
29/06/2022 17:18:03
Walking
2
1
29/06/2022 17:18:02
Walking
3
1
29/06/2022 17:18:01
Start
4
1
28/06/2022 19:21:03
...
- 35
0
votes
2
answers
773
views
Postgres sort results by id's from subquery
In postgres 11 im implementing something in type of suggesting new tag for user. To achieve that im first counting amount of activities of specific user in specific category. Query looks as follows
...
- 15
0
votes
0
answers
216
views
Efficiently sort by a column whose sort order almost matches primary key in PostgreSQL
We have a table of events in PostgreSQL 14 that looks like this
event (event_id integer primary key, event_time integer, ... other columns)
event_id is automatically set from a PG sequence, so it ...
- 210
3
votes
2
answers
966
views
Why MySQL 8 doesn't return any rows when using a table with a descending PRIMARY KEY, 2 indexes, and valid join conditions with existing rows
Using 2 tables (A & B), joinable by their PK, with the PRIMARY KEY of A being sorted DESC (PK in descending order as it's allowed by MySQL 8), and A having 2 indexes in ASC ordering.
When I ...
- 209
-1
votes
1
answer
326
views
What database / technology should I use if I want to read the top N records sorted on some integer key?
What database / technology should I use if I want to read the top N records sorted on some integer key?
Currently, my data is stored using mongodb.
I don't have any limitations on technology except ...
- 127
1
vote
1
answer
538
views
How to sort all rows, one-per-group in a loop?
I'd like to sort all rows in a table, one per category in a loop. For example, given the following data:
+---------+-------------+
| item_id | category_id |
+---------+-------------+
| 4013738 | ...
- 736
2
votes
4
answers
6k
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 ...
- 825
1
vote
2
answers
182
views
MySQL:memory used by query joins does not get released
I have a MySQL db running on a 8cpu 24Gb machine.
There are multiple scripts writing or reading from it at any point in time, often in parallel.
My settings:
[mysqld]
innodb_buffer_pool_instances = 6
...
- 173
3
votes
1
answer
2k
views
SQLite, Sort by Index, faster without index
I'm just reading this excellent guide to relational database query planners and indexes on SQLite site. In section 2.2. Sort By Index it says the following about sorting by index:
But does this ...
- 145
3
votes
1
answer
12k
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&...
- 33
-3
votes
1
answer
90
views
How to arrange documents in a collection alphabetically in mongodb?
How do I arrange my document names in a collection alphabetically and henceforth make my searches for a particular document faster?
Say I have a million documents in a collection. I want to search for ...
-1
votes
2
answers
2k
views
MYSQL: Query the last conversion on chat messages and sort by latest message
I am having issue on query sorting of last convo messages of each users.
Here is my current record on my 2 tables:
I want to achieve below result:
My current query is:
SELECT `messages`.*, `users`.`...
0
votes
1
answer
48
views
Consolidate / sort / combine / merge data for dummies
I am looking for a non-scripting solution to combine data from 3 different tables (preferably using spreadsheet software like Microsoft Excel but a database would also work e.g. Microsoft Access). I ...
- 103
0
votes
1
answer
2k
views
mysql creating sort index making queries slow
We have a view table the query for the view is pasted below. The problem is that whenever we try to query items view our database start making sorting index which make the over all time of our ...
3
votes
2
answers
322
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
19
votes
3
answers
12k
views
How to treat numbers within strings as numbers when sorting ("A3" sorts before "A10", not after)
For all of these queries:
SELECT label FROM personal.storage_disks ORDER BY label ASC;
SELECT label FROM personal.storage_disks ORDER BY label COLLATE "C" ASC;
SELECT label FROM personal....
- 715
2
votes
2
answers
20k
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 ('{ "...
- 43
1
vote
2
answers
342
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
0
votes
2
answers
397
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
2
answers
25
views
Help ordering table query
I have a MySQL table with this structure:
Gene | Impact | Frequency
---------------------------
SCN1A | LOW | 0.8
---------------------------
CFTR | MODERATE | 0.1
-------------------------...
3
votes
1
answer
5k
views
Select with CROSS APPLY runs slow
I am trying to optimize the query to run faster.
The query is the following:
SELECT grp_fk_obj_id, grp_name
FROM tbl_groups as g1
CROSS APPLY (SELECT TOP 1 grp_id as gid
...
- 133
1
vote
1
answer
783
views
How do I Order By on multiple rows in Postgresql
I have a table with columns with severities like:
name | critical | major | trivial
----------------------------------------
Apache |2 |3 |2
Eclipse|5 |0 |4
TestNg |0 ...
0
votes
1
answer
35
views
SELECT with comparison operator, based on an order, skipping row(s)
I have a resultset from a SELECT query, based on a certain ORDER BY condition, and I’m trying to check the integrity of the order in which the resultset is rendered. First, I’m specifying a LIMIT, on ...
- 101
1
vote
1
answer
436
views
Avoid Sort on Indexed Group By caused by DATE_TRUNC
Is there a way to avoid Sort operation for GROUP BY, caused by the DATE_TRUNC function not being recognized as tstamp, which is a part of the ix index? That function doesn't really affect the order ...
- 35
1
vote
1
answer
459
views
How can I first sort by color & then in alphabetical order in ssrs?
I'm still new to ssrs. In my query the color is determined by location. In my query I did
ORDER BY [Location], Underwriter. Now this works perfectly fine in my query, but when I run it in SSRS it ...
- 11
0
votes
1
answer
233
views
mysql, select and sorting by group key + with first row that has that key in another column
I'm trying to make a select that outputs everything, but is sorted by a group column with a "header column" that matches said group column on another column.
I have no idea how this type of query ...
- 127
0
votes
1
answer
565
views
Sybase ASE 15.7 sort ordering
I have my sort ordering in Sybase as:
45 190 altdict - Alternate (lower-case first) dictionary ordering
Am I right that alternate means "aAbBcC..zZ" ordering?
What should I do to change ordering ...
- 430
1
vote
2
answers
8k
views
Mongodb Sort Array Objects within Array using expression
Please help to make query in mongodb.
I want mongo query in which sort array objects using some expression.
Example.
I have documents like
{
"_id:"...
- 135
0
votes
1
answer
78
views
ORDER from 2 tables to achieve a specific order returned by an API
There's an external API that returns image similarities, based on an "image ID" and a "image version" (a same image can have multiple versions).
The similarity ORDER is given by the API.
The API ...
- 3
0
votes
1
answer
187
views
PosgreSQL query slow when sorting on indexed timestamp colum
I've the following issue: the table below:
CREATE TABLE public.current_read
(
id bigint NOT NULL DEFAULT nextval('current_read_id_seq'::regclass),
cube_id bigint NOT NULL,
end_time ...
- 3
0
votes
0
answers
96
views
When does setting the SORT_IN_TEMPDB option actually improve performance? [duplicate]
My question is related to index rebuilding, mainly the SORT_IN_TEMPDB option.
BOL states that:
Even if other users are using the database and accessing separate disk addresses, the overall ...
- 1,303
0
votes
1
answer
510
views
Postgres, `where` condition, sort by first column, but I don't want that
I am using PostgreSQL 11.5 on Ubuntu
Issue
I have sql with joining tables and with where condition, which looks like this.
...
where
s_product.product_shop_id in (
'237002',
'230041',
'467173',
'...
- 191
1
vote
2
answers
1k
views
primary key constraint Sorting
Is having a field in primary key constraint optimizes sorting of the table ?
Here's the schema of the table:
CREATE TABLE Persons (
Field1 long NOT NULL,
Field2 long NOT NULL,
Field3 long NOT NULL,
...
- 113
0
votes
1
answer
880
views
Mongodb replace default _id with string
I replace MongoDB default _id with type string. And now I want to sort my collections, the result _id after sort is:
1
10
100
101
2
20
200
I want the result like sorting number which is:
1
2
3
4
5
...
- 103
1
vote
1
answer
237
views
Redundant fields in ORDER BY for efficient sorting?
Suppose I have a table SortTest with fields Data1, Data2, Sort1, and Sort2, Key1 and Key2 need to perform the following query:
SELECT TOP 1
Data1,
Data2
FROM SortTest
WHERE Key1 = @key1 AND
...
- 248