The tag has no usage guidance.

learn more… | top users | synonyms (1)

1
vote
0answers
42 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
75 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
13 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 ...
2
votes
1answer
51 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
73 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
79 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 (...
0
votes
0answers
44 views

How to add an index to attributes sorted on in mongodb

Before you find another reason for changing the category of this question, it was on Stack Overflow and it was described as a DB Admin question, so here goes: I am running a reporting app with ...
3
votes
0answers
87 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
70 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, ...
4
votes
1answer
180 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
30 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 ...
4
votes
2answers
206 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
81 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 ...
6
votes
5answers
502 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 ...
2
votes
1answer
1k 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 ...
1
vote
0answers
77 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 ...
1
vote
0answers
18 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),...
6
votes
1answer
94 views

Why is DistinctSort followed by TopNSort in my query plan?

I have the following definition: CREATE TABLE [dbo].[JobItems] ( [ItemId] UNIQUEIDENTIFIER NOT NULL, [ItemState] INT NOT NULL, [ItemCreationTime] DATETIME ...
2
votes
1answer
830 views

how to use index to speed up sorting in postgres

I am using postgres 9.4. The messages has the following schema: messages belongs to feed_id, and has posted_at, also messages can have a parent message (in case of replies). ...
2
votes
2answers
178 views

DB2 sorting by lowercase uppercase - problem

I'm struggling with DB2 - sorting by Polish letters with diacritics and uppercase/lowercase. When I created a database using: CREATE DATABASE P1DS8 AUTOMATIC STORAGE YES ON '/home/db2inst1' DBPATH ...
0
votes
1answer
39 views

Sorting on compound Indexes

This may be a novice question but could it be possible query here. If I have a compound Index as { a: 1, b: 1, c: 1, d: 1 } Is this query possible? db.data.find( { a: {$gte:1,$lt:2}, b: { $gt: 3} ...
1
vote
0answers
24 views

Custom cycling order in MySQL

Having following Foo table: id | name | group ===|=========|====== 1 | "John" | 1 2 | "Paul" | 1 3 | "Marry" | 1 4 | "Josh" | 1 5 | "Mike" | 2 6 | "Kevin" | 2 7 | "Ramon" | 2 8 | "Kim" ...
2
votes
1answer
752 views

MySQL : Avoid Temporary/Filesort Caused by GROUP BY Clause in Views

I'm planning on creating a view using this SQL SELECT, but the explain for it shows it's using temporary and using filesort. I can't figure out what indices I need in order to fix this problem. Mostly,...
13
votes
2answers
628 views

Paging performance with customizable sorting over many millions of rows

In our application we have a grid where users can page over a large number of records (10-20 million). The grid supports sorting in ascending and descending order in a number of columns (20+). Many of ...
2
votes
1answer
130 views

Sorting by two columns conditionally [closed]

Column a has category ids, column b has set orders: +-------------+-----------+ | category_id | set_order | +-------------+-----------+ | 80 | null | | 81 | null | | ...
2
votes
1answer
194 views

Threshold for sort spill and random cardinality estimates

I wanted to test tempdb spill warnings so I run the following script on SQL Server 2014: USE tempdb IF OBJECT_ID('tempdb..tblTest') IS NOT NULL DROP TABLE tblTest CREATE TABLE tblTest ( ...
2
votes
0answers
123 views

Schema for user drag and drop ordering

I'm trying to work out the schema for a system where users will be able to order items in an (unlimited length) list by drag and drop. Is there any kind of standard way of storing this ordering ...
1
vote
1answer
136 views

SQL Server Query performance with window function

We write a query that include unpivot, partition by and order by.Query is: SELECT PersonId ,SalaryDate ,ID ,Type ,SalaryValue ,ROW_NUMBER() OVER ( PARTITION BY PersonId ...
1
vote
1answer
208 views

How to sort rows to appear alternating (e.g. A B A B … )?

Suppose I have a column x with values A and B. For example: Row X 1 A 2 A 3 A 4 B 5 B 6 B Now, I want to sort them into this fashion: Row X 1 A 2 B 3 A 4 B 5 A 6 B Of ...
0
votes
0answers
52 views

Speed up sorting in this query

I'm trying to optimize the following plan and noticed that PostgreSQL spends a lot of time (relatively speaking) on sorting before the merge joins. Is there a way (using indexes, cluster the table, ...
3
votes
3answers
751 views

How to ORDER BY typical software release versions like X.Y.Z?

Given a "SoftwareReleases" table: | id | version | | 1 | 0.9 | | 2 | 1.0 | | 3 | 0.9.1 | | 4 | 1.1 | | 5 | 0.9.9 | | 6 | 0.9.10 | How do I produce this output? | id | ...
2
votes
1answer
947 views

Sort Warnings, finding out the query behind them

I'm troubleshooting a SQL Server 2012 that has started timing out as of a few weeks ago. The server is filled with thousands of sort warnings, at a rate of about 15 warnings per minute. To get the ...
0
votes
1answer
66 views

Sort within row

I wonder, if there is a way to sort data inside each row? For example, I have table val1, 3, 2, 1 val2, 2, 3, 1 val3, 3, 1, 2 val4, 1, 3, 2 val5, 1, 2, 3 and want to select it sorted, as val1, 1, ...
4
votes
5answers
15k views

How to determine the optimal sort_buffer_size?

I read from a sample configuration file which says the following: # Sort buffer is used to perform sorts for some ORDER BY and GROUP BY # queries. If sorted data does not fit into the sort buffer, a ...
1
vote
1answer
162 views

Sorting a list of records alphabetically: is there any reason NOT to let the database do it

I have a list of records that contain a column of arbitrary strings. I would like the application to display them sorted in some order (preferably alphabetically, since humans have to read the data). ...
0
votes
1answer
48 views

Sorting by a field in child table

There are two tables: contract (with primary key contractid) and contractupdate (with field contractid referring to a contract). contractupdate also has DATE field startdate and TINYINT field day. I ...
0
votes
0answers
2k views

MySQL: High sort_buffer_size setting value

Thing is I was getting plenty of 131104 20:10:36 [ERROR] /usr/sbin/mysqld: Sort aborted with 256M and rather erratic behaviour in my load test. Now at sort_buffer_size = 1024M ...
2
votes
1answer
4k views

Order by alphabet and then by numbers

I have records like A5 A4 Z1 B2 C7 C1A C11A B1 B4 I want them to be sorted in this manner A4 A5 B1 B2 B4 C1 C11A C7 Z1 using the ORDER BY clause. I want them to be sorted by alphabets ...
1
vote
2answers
3k views

Order by and then Concat

I have a table table1 like: col1 col2 A1 A2 A1 B1 A1 B2 A2 A1 A2 B1 A2 B2 B1 A1 B1 A2 B1 B2 B2 A1 B2 A2 B2 B1 I need to concat col1 and col2 such a way that it ...
1
vote
3answers
2k views

MySQL InnoDB Sorting Issue

I was surprised to see MySQL's InnoDB showing an interesting behavior, which I cannot fully explain. According to the official MySQL InnoDB Documentation: "All indexes other than the clustered ...
0
votes
0answers
63 views

Faster way to GROUP or ORDER dynamic fields?

We have this query in our app, that searches a table for a song title using MATCH AGAINST and picks the most relevant results. The table has around a million results and is growing. The app has been ...
1
vote
1answer
2k views

Sorting Result state taking very long time

I am using a MySQL 5.1.69-log DB for analysis purpose. I've designed my database in a star schema. All tables are using InnoDB. My innodb_buffer_pool_size=2Gb This is the query I am running : ...
1
vote
0answers
103 views

Can't get rid of filesort on joined query sorting on second table

For some reason, this query: EXPLAIN SELECT * FROM biz as b INNER JOIN listings as l ON b.id = l.biz_id WHERE l.pub_id = 14 AND b.cat_id=310 ORDER BY l.level DESC, l.random DESC LIMIT 5; says it ...
1
vote
2answers
3k views

Estimated Execution Plan SQL Server Sort?

I'm running a query and its taking a age to execute, looking at the execution plan I can see that 51% of the cost is in the SORT? when in the actual query i am not ORDERING BY anything. Anyone got ...
1
vote
1answer
2k views

db INDEX on ORDER BY FIELD in very simple query does not help - still slow response?

I have very simple query which returns 200K records very slow (20 seconds). SELECT * FROM TABLE ORDER BY ID DESC If I do just SELECT * FROM TABLE it returns quick result. I created INDEX on ...
5
votes
2answers
124 views

Algorithmic order of table indexes for table operations

I have not been able to find a clear indication of the impact table indexes have on the algorithmic order of table operations. I am not interested in the nuts & bolts of specific implementations; ...
2
votes
1answer
401 views

sorting groups of related rows by average values while keeping the groups together

I'm using PostgreSQL 8.4, but would like a standard SQL solution if possible. Consider the following table. corrmodel=# SELECT * from data limit 1; id | datasubgroup_id | datafile_id | ...
6
votes
4answers
9k views

Slow query on large table with GROUP BY and ORDER BY

I have a table with 7.2 million tuples which looks like this: table public.methods column | type | attributes --------+-----------...
11
votes
1answer
5k views

How to preserve the original order of elements in an unnested array?

Given the string: 'I think that PostgreSQL is nifty' I would like to operate on the individual words found within that string. Essentially, I have a separate from which I can get word details ...
3
votes
1answer
1k views

MongoDB geospatial query with sort - performance issues

I have query (which is very slow ~2,5s): db.markers.find({ latlng: { '$within': { '$box': [ [ -16, -140 ], [ 75, 140 ] ] } } }).sort({_id: -1}).limit(1000) When I run explain for this query I get {...