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

Questions tagged [sorting]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
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) | +-----------+------------------+---------...
Henry's user avatar
  • 213
0 votes
0 answers
41 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... ...
SnackK0515's user avatar
0 votes
1 answer
110 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... ...
SnackK0515's user avatar
1 vote
2 answers
77 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
0 votes
2 answers
67 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 ...
ioxoi's user avatar
  • 9
0 votes
2 answers
68 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: { ...
Bear Bile Farming is Torture's user avatar
1 vote
2 answers
125 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 ...
Morgeth888'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
17 views

How to Auto sort on one field in MongoDB?

I am new to the MongoDB database. Because the number of records is very large, it takes us a lot of time to sort them in each query. I want to have a field called Id, like in SQL server, that I can ...
hmahdavi's user avatar
  • 107
0 votes
1 answer
863 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 ( ...
Lance's user avatar
  • 209
0 votes
1 answer
47 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:/...
trogwar's user avatar
  • 103
0 votes
2 answers
898 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
1 vote
1 answer
57 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 ( ...
kicaj's user avatar
  • 123
3 votes
2 answers
586 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 @ ...
Amit Keinan's user avatar
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 ...
Ruan's user avatar
  • 35
0 votes
2 answers
647 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 ...
LSM2236's user avatar
  • 15
0 votes
0 answers
189 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 ...
EM0's user avatar
  • 190
3 votes
2 answers
898 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 ...
DarkCenobyte's user avatar
-1 votes
1 answer
259 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 ...
libby's user avatar
  • 127
1 vote
1 answer
515 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 | ...
Eaten by a Grue's user avatar
1 vote
4 answers
5k 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
  • 775
1 vote
2 answers
163 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 ...
Vincent Teyssier's user avatar
3 votes
1 answer
1k 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 ...
woky's user avatar
  • 145
3 votes
1 answer
10k 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&...
lrai's user avatar
  • 33
-3 votes
1 answer
85 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 ...
Vishwa Mithra Tatta's user avatar
-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`.`...
Larvenrez Bbp's user avatar
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 ...
Ad Astm's user avatar
  • 103
0 votes
1 answer
1k 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 ...
Sabih Ahmed's user avatar
3 votes
2 answers
282 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 ...
Rouzbeh Zarandi's user avatar
17 votes
3 answers
10k 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....
user15080516's user avatar
1 vote
2 answers
17k 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 ('{ "...
nesiax's user avatar
  • 33
1 vote
2 answers
306 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" ...
Jan's user avatar
  • 113
0 votes
1 answer
317 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 ...
loicEzt's user avatar
0 votes
2 answers
24 views

Help ordering table query

I have a MySQL table with this structure: Gene | Impact | Frequency --------------------------- SCN1A | LOW | 0.8 --------------------------- CFTR | MODERATE | 0.1 -------------------------...
Juan Fº Quesada Espinosa's user avatar
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 ...
EtherPaul's user avatar
  • 133
1 vote
1 answer
739 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 ...
underachiever's user avatar
0 votes
1 answer
34 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 ...
Roshan Upreti's user avatar
1 vote
1 answer
402 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 ...
Nikola's user avatar
  • 35
1 vote
1 answer
401 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 ...
user203633's user avatar
0 votes
1 answer
225 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 ...
J. M. Becker's user avatar
0 votes
1 answer
529 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 ...
Mikhail Aksenov's user avatar
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:"...
5a01d01P's user avatar
  • 135
0 votes
1 answer
76 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 ...
Daaaaa's user avatar
  • 3
0 votes
1 answer
173 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 ...
fritz's user avatar
  • 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 ...
Rauf Asadov's user avatar
  • 1,273
0 votes
1 answer
465 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', '...
genderbee's user avatar
  • 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, ...
Ani's user avatar
  • 113
0 votes
1 answer
858 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 ...
roscoe_x's user avatar
  • 103
1 vote
1 answer
228 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 ...
Anton Shepelev's user avatar
0 votes
2 answers
120 views

Sorting ranking by trigger

I create a game in which players will gain levels. The basic information is in the table "char" (nickname, lvl, exp, money ...). I want to make a ranking that will be updated on a regular basis and ...
dolanoadriano's user avatar