Questions tagged [sorting]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1
vote
2answers
72 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, ...
0
votes
1answer
38 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 ...
1
vote
1answer
78 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 ...
0
votes
0answers
28 views

PostgreSQL pg_trgm sorting

I am using a gin_trgm_ops index on a varchar(255) column. I am having trouble sorting the results. For example if I look up similarity(name, 'samsung ssd') as similarity I get varying results. ...
0
votes
2answers
24 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 ...
1
vote
2answers
51 views

postgresql random function returns duplicate results when order by random used in query

I'm using postgresql 11.1-1 on Windows 10. On my system, if I run a query like the following, I will get a different number for each random column: postgres=# SELECT random() as sortOrder, random() ...
14
votes
1answer
376 views

Sort spills to tempdb but estimated rows equals to actual rows

On a SQL Server 2016 SP2 with max memory set to 25GB we have a query that executes about 80 times in a minute. The query spills about 4000 pages to tempdb. This causes for a lot of IO on the disk of ...
2
votes
1answer
157 views

ORDER BY ASC is 300x faster than ORDER BY DESC

The ascending sort is 300x faster than descending sort on a column which has a B-Tree index. I have table of around 20 million rows. I have indexes on inserted_at and x columns. Ascending Sort ...
-1
votes
1answer
22 views

custom sorting on column name [closed]

Here I have sample data like Plan1 - 1 Plan1 - 3 Plan1 - 2 Plan1 - 5 Plan2 - 4 Plan2 - 1 I need to show like this Plan1 - 1 - 0 Plan1 - 2 - 1 Plan1 - 3 - 2 Plan1 - 5 - 3 Plan2 - 1 - 0 Plan2 - 4 - 2 ...
1
vote
1answer
281 views

MySql sort conditional based on column values

I have a table with products where there's a column for price and stock. I want the products to be display sorted by the following rules: All products with price = 0 go to the bottom; Then the ...
0
votes
1answer
445 views

Calculations with group-wise sum and row count

Is it possible to sort a table by a value, divided by the sum of values within each year, again divided by number of rows for each year? I've tried to illustrate it by this faulty pseudo-query: ...
2
votes
2answers
5k views

Rule of thumb for tmp_table_size and max_heap_table_size MySQL properties

The title pretty much sums up the question itself: Is there a rule of thumb regarding the values for tmp_table_size and max_heap_table_size MySQL properties? I've had a serious performance ...
-2
votes
2answers
83 views

Sorting A Column with all null values have strange behavior [closed]

There is a column in my table, where all the values in a particular column are null. Now when I sort by this column ASC and DESC, I should get the Same result as per my assumption. But I'm getting ...
0
votes
2answers
553 views

How to update “sorting_order” column of other rows when changing one?

I need to set a custom order sequence for categories when adding new ones, and changing existing ones. I believe the code and sql query will be the same in both scenarios. In my webshop_categories-...
0
votes
1answer
21 views

recreate row IDs in sorted table

I built word2vec dictionary as a mysql table. There is millions of rows like this: ID CREATED UPDATED KEYWORD COUNT NUMPYBLOB NUMPYBLOB ... ... 121201 ...
0
votes
1answer
52 views

Another question about *_buffer_size

Mysql-5.6.30 running on Debian, uses InnoDB, DB size about 100Gb. Dual Xeon 5645 with 48Gb RAM, 4 HDD 15K in RAID 10 (/var) and system on RAID1 There're many "heavy" ORDER BY/GROUP BY, JOIN/sort ...
1
vote
1answer
2k views

PostgreSQL external sort performance

I'm trying to figure out external sort performance and it's dependence on WORK_MEM value. I've tested that increasing WORK_MEM doesn't always speed up external sorting (example below, tested on ...
3
votes
4answers
3k views

Reorder records in table

I am looking for a SQL solution to reorder records by updating order column. Consider following data structure: ------------------------------------ | id | type | value | order | user | -------------...
2
votes
2answers
223 views

Sort Code Reuse for Multilingual Database

I'm attempting to sort a result from a table that is multilingual. I would like to have the sorting algorithm come from a function but then it adds a 8% hit to the performance, at least. So, I'm not ...
1
vote
2answers
41 views

Sort table so that linked rows show up as subsets in overall alpha sort

I have this member table where memberships are either single or family, and if family they can be family head or dependent. When outputting a view of the membership, it currently sorts alpha ascending,...
0
votes
1answer
75 views

The opposite of ordering by a column in SQL

If you have a table and you'd like to sort by a column you can use order by foo to order the results by foo. Is it possible to do the opposite, such that rows which have the same value for foo would ...
0
votes
2answers
562 views

MySQL SELECT and SORT performance - v. large table

I have a table that contains in the region of 4 million rows. This is searched using a form, and then the results are returned. However, the query seems to take an inexplicably large amount of time, ...
0
votes
1answer
94 views

How to support arbitrary column sorting in a database?

Our product has a "table view" feature that allows customers to display their data in tabular form. That data is made up of form fields the customer creates, and thus there can be an arbitrary number ...
-1
votes
3answers
183 views

Sort MySQL select from another table data

I have 2 tables that involve this query. Table : Discussions Table : Comments -------------------- ------------------------------------- |id | content | | id | ...
1
vote
1answer
425 views

Get user rank handling equal scores

Imagine there is a table with users logins and their scores: +----+-------+-------+ | id | login | score | +----+-------+-------+ | 1 | log1 | 4 | +----+-------+-------+ | 2 | log2 | 4 | +...
4
votes
2answers
3k views

Why does Inner Join introduce a hidden sort?

Consider the following example: DECLARE @suppliedNumber MONEY SET @suppliedNumber = 245656.00 SELECT ah.FirstName, ah.LastName FROM AccountHolders AS ah JOIN [dbo].[Accounts] AS ...
0
votes
1answer
132 views

How to cache select with variables? (MySQL)

Context: I have complicated very often executed query: CREATE OR REPLACE VIEW ordered_words AS SELECT word.id FROM category JOIN frequency ON category.id = frequency.category_id JOIN word ON ...
7
votes
2answers
2k views

Query memory grant and tempdb spill

I have a long running query (fact table with 100 million rows joining a number of small dim tables then group by) that's spilling to tempdb, even though (after some tweaking) the CE is very close to ...
3
votes
2answers
2k views

Re-order columns values based on values in a table

I would like know, how can we re-order the columns in ascending or descending order, while retrieving the data. Suppose the table contains the data as below with no primary key/unique key constraints:...
2
votes
1answer
1k views

how to sort by timestamp field while counting duplicates

I have a table with username, timestamp and action columns. I need to retrieve a list of usernames, total occurrences of each user and the latest timestamp for each user. The following statement works ...
1
vote
0answers
34 views

Case-folded sorting of output columns containing expressions

Using the following query as simple example, SELECT l.id, string_agg(lc.country, ' ' ORDER BY lc.country) AS countries FROM licenses l JOIN license_countries lc ON ...
1
vote
1answer
561 views

How to sort strings with special characters in Hibernate?

For example, if the Order in criteria is ascending and if we have two names like follows: _zname_9 1_aname_ I get an output (from hibernate result) like: _zname_9 1_aname_ But the expected output (...
3
votes
2answers
126 views

Using floats for PKs. Is it a good idea?

Let's say I have an entity PizzaStore which has a location as part of its attributes Now a PizzaStore can have N of other PizzaStore that are close by. So seems to be a self referencing relationship 1-...
-2
votes
1answer
82 views

Get user rankings based on points from database

I've created a website where members collect points to get a reward at the end of the contest. I want to create a page that shows the top 10 members who have the most points. I've tried a few pieces ...
1
vote
1answer
71 views

Keep one row per ID while keeping previous order (from ORDER BY)

I'm actually working on a big query and stuck at the very end. I'm working on MySQL 5.7. ID // SubID // Criteria 1 // Criteria 2 // ... // Critera n 1 // 2 // x // y // ... // ...
1
vote
2answers
951 views

High CPU usage from sorting result queries

I have two machine have two master-master mysql replicate, only problem i have that i got massive queries like "sorting result" queries and i don't know how to fix these queries . mysql process cpu ...
2
votes
0answers
285 views

How does the use of derived tables speed up sorting?

In the article 45 Ways To Speed Up Firebird Database, it states that Use derived tables to optimize SELECT with ORDER BY/GROUP BY Another way to optimize SQL query with sorting is to use ...
3
votes
1answer
85 views

SQL Merge Statement Spending Large Amount of Time Sorting on Primary Key

We are looking to use the SQL MERGE statement in SQL Server 2012 to handle data replication for a data warehouse that we have access to from the manufacturer of a 3rd party application we use for data ...
2
votes
2answers
165 views

Very tricky: Can't update a sequential list SQL-92 ( Filemaker)

I have a quite simple problem, but the solution is very tricky. I'm trying to solve this for months: There is a column "SortID" that needs to get updated with sequential integers, starting from 1. ...
0
votes
2answers
2k views

Sort by Start Date Desc if End Date is null, if not Sort by End Date

I have a SQL Server (RDBMS) table with a list of Activities. Each activity has a Start and End Date. The requirement is to sort them is order such that the ones with a NULL end date display first (...
3
votes
1answer
157 views

Sort operation SQL Server 2012

I am trying to optimize a query and the sort operation (91% cost) is the costliest operation. Any help will be appreciated. Query is : SELECT TOP 1 ArchiveType, CONVERT(NVARCHAR(30), A....
1
vote
1answer
84 views

Ordering in postgresql

I'm facing an issue whereas I do not understand. Running PostgreSQL on mac and have this query: select tldid, tldaction, to_char(tldactiondate, 'dd/mm/YYYY') as tldactiondate, ...
9
votes
1answer
5k views

ORDER BY and comparison of mixed strings of letters and numbers

We need to do some reporting on values that are usually mixed strings of numbers and letters that need to be sorted 'naturally'. Things like, e.g. "P7B18" or "P12B3". @The strings will mostly be ...
1
vote
1answer
45 views

Update all rows when sort/ordering value is changed for single row?

I am in the process of creating a GIS application with multiple layers and many pieces of content shapes on each layer. Currently each layer/shape is drawn in the order that it was returned from the ...
5
votes
2answers
2k views

Oracle sort order changed

We recently deployed a new environment with a newer version of Oracle (12c) instead of 11r2. Only recently I noticed my data returned is sorted differently. Instead of having small letters, capital ...
0
votes
1answer
827 views

Postgres datetime sort slow and not using index

Using Postgres 9.4, I'm trying to order a query by an indexed datetime column. explain SELECT g.*, array_agg(gp.platform_id) as platform_list, array_agg(gm.metacritic_id) as ...
8
votes
5answers
9k views

SQL queries to only show most recent purchase records for individual food items

I'm working with a food purchasing / invoice system in MS Access 2013 and am trying to create an SQL query that will return the most recent purchase price for each individual food item. Here is a ...
3
votes
1answer
4k views

MySQL pivot query trouble sorting column by total

I am trying to return results for a matrix that represents the net weight that a country imports from another country. I have managed to write a query that produces a pivot type table in mysql all ...
2
votes
1answer
736 views

HQL pagination returns same result on different pages - with SQL

We have the following query in the server (using sql): Query query = this.entityManager.createQuery( "SELECT type, id, date, amount FROM Transaction ORDER BY type ASC"); The type field is ...
1
vote
0answers
34 views

Access algorithms in Partial Index implementation of PostgreSQL 9.4?

I am studying big-O time-space complexities of different algorithms with the partial index of PostgreSQL 9.4: introsort (O(n log n)-time, O(log n)-space) and heapsort (O(n log(n))-time O(1)-space), ...