Questions tagged [order-by]
A clause used in SQL SELECT statements to sort query results.
358
questions
1
vote
2answers
65 views
SQL Server order of execution and query plan
I am aware that the order of execution of a query in SQL Server is (excluding TOP, etc):
FROM, JOIN
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
However, I cannot understand how in the query plan the sort (...
1
vote
2answers
27 views
A SQL query optimzation issue with ORDER BY
An SQL query optimzation issue with ORDER BY
Current status
I have a database of Chess games and their corresponding moves (stored as strings called fens). I have two main tables 'Game' and '...
7
votes
2answers
345 views
SELECT DISTINCT ON, ordered by another column
Please consider the following table test:
CREATE TABLE test(col1 int, col2 varchar, col3 date);
INSERT INTO test VALUES
(1,'abc','2015-09-10')
, (1,'abc','2015-09-11')
, (2,'xyz','2015-09-12')
, (2,...
0
votes
1answer
20 views
Select value in Left Join ordered by Date
I am trying to LEFT JOIN a value to sort by the most recent date, but for some reason it is only returning a result for the last returned result.
These are my table layouts
cs_users
+---------+-----...
0
votes
1answer
28 views
Pagination while sorting a non unique text column
What are the ways to paginate a table on a non unique text column?
On a unique timestamp column, one can create an index on it and execute the following to move forward
SELECT * FROM users WHERE ...
0
votes
2answers
27 views
How to sort aggregate function call
If I have an expensive function call that takes a JSONB and returns an INT, e.g.
CREATE FUNCTION my_expensive_function(data JSONB) RETURNS INTEGER AS
How do I go about ordering the aggregate result ...
0
votes
1answer
145 views
ORDER BY items must appear in the select list if SELECT DISTINCT is specified
SELECT DISTINCT *
FROM (
SELECT a.Title, a.ID FROM TableA a
LEFT JOIN TableB b
ON a.ID = b.XID) c
ORDER BY
CASE WHEN c.ID > 10 THEN c.Title
ELSE c....
0
votes
2answers
40 views
How to save working hours of multiple stores and then query it without duplication?
I'm want to create database with stores. Each store has their own working hours in a week. Then I want to query the store and sort them by nearest store which is currently open. So far I create this ...
0
votes
2answers
39 views
Does the 'ORDER BY' clause of a subquery pass through to the main results (POSTGRES)
I've got a query written something like this.....
with subquery as
(
SELECT A, B, C, D,
row_number() OVER(PARTITION BY X ORDER BY Y) as RN
FROM blah blah
ORDER BY A,B
LIMIT 100
)
SELECT C,...
0
votes
1answer
111 views
How to reorder rows in Postgresql
I've got the following table
And I have to change the order of the rows in an unpredictable way for example:
Set the position of the row with id = 1 after the row with id = 3 and preserve the ...
0
votes
1answer
30 views
ORDER BY with IF-ELSE statement (DESC doesn't work)
Descending sort type does not work with this syntax, please help.
<?php
require_once("../connection/conn.php");
$compare = $_GET["sortfield"];
if(isset($_GET["sortfield"])){
if(isset($_GET["...
0
votes
1answer
44 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 ...
0
votes
2answers
26 views
Does reordering table rows reduce the time for subsequent ordering with the ORDER BY clause?
Is there any run-time advantage gained by changing the order of a table's rows to match the expected ordering of the ORDER BY that is in a slow select?
(assuming unique alphanumeric index and no auto-...
1
vote
1answer
58 views
Determine Order Streak
For a particular customer, I have data returned by a query that looks like this:
OrderDate OrderCount
2020-01-12 0
2020-01-13 17
2020-01-14 22
2020-01-15 21
2020-01-16 30
2020-01-17 12
2020-...
0
votes
1answer
23 views
MySQL query help
I need a query that returns the number of liked messages and orders them by the number of likes (from most to least).
I have tried:
SELECT users.user_id,
messages.author,
messages....
1
vote
1answer
73 views
Is SELECT FOR … UPDATE *necessary* to DELETE or UPDATE multiple rows without deadlock?
Consider the following when the transaction isolation level is Read Committed (the default):
UPDATE example
SET foo = 'bar'
WHERE ...
and
DELETE FROM example
WHERE ...
Neither DELETE nor UPDATE ...
0
votes
1answer
61 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 ...
0
votes
2answers
23 views
Can I tell postgres to use a different “collation” for an integer column?
Let's say I'm using an integer column to represent an "enum" in my application code, for a "priority" attribute.
0=low
1=medium
2=high
The app is operating for a while like that, and then I want to ...
3
votes
1answer
96 views
Why does ORDER BY NULLS LAST affect the query plan on a primary key?
Using PostgreSQL 11, I have the following table with around 450 million rows:
postgres=> \d+ sales
Table "public.sales"
...
0
votes
1answer
36 views
Ordering by field, constructed by subquery is slow
My tables (oversimplified for clarity):
documents: registrations:
|id| |id|document_id|date|
My simplified query:
SELECT *
FROM (SELECT documents.*,
(SELECT max(date)
...
0
votes
0answers
39 views
Order By not working correctly in common table expression in Fetch clause while retrieving data
I have been working on a stored procedure optimization which fetches data from multiple tables with many left joins.
The problem I am facing is that in common table expression while fetching the ...
3
votes
0answers
48 views
MySQL uses INDEX for optimizing ORDER BY with GROUP BY except when you add a JOIN
Question
Why can't Query #2 use the same (car_trims.horsepower_peak) index to optimize the sorting of the rows as Query #1? The only difference between the two queries is the addition of the JOIN in ...
1
vote
1answer
86 views
MYSQL - How to optimize VIEW performance?
Total profiles - 100000
Each profile is connected to one or more platform
Each profile have 50 Tags
Each profile have 4 themes
I have a user view (total users around 100000) where I am joining all ...
0
votes
0answers
12 views
I'm trying to order a table with 3 columns as if the columns were merged into 1 such that it would sort as 3 separate entries
I've hit a somewhat unique obstacle with my current project. Below is a mock MySQL table to better describe the issue I'm trying to solve.
mysql> describe `minimized_event_instructions`;
+---------...
1
vote
2answers
59 views
MYSQL: How to optimize a date sorted table with a triple primary key index
I have been maintaining a MYSQL database for a few years, dating from back when i took database managing classes. Its biggest table has since grown (15 million entries for 1,8 GB) and this query began ...
2
votes
2answers
74 views
Select random database records between subset of records having the same count(*) in a query
I have this simple query:
SELECT `value`, count(*) as `noHits`
FROM `topic`
WHERE identifier IN ('" . implode("','", array_keys($processedIdentifier)) ."')
GROUP BY `value`
...
0
votes
1answer
141 views
Order By First Condtition and If multiple matches, order by second condition
I was practicing SQL problems at hackerrank.com when I came across the following question:
Query the Name of any student in STUDENTS who scored higher than
75. The Marks field in the table stores ...
0
votes
2answers
39 views
Order by is ignored if inside a derived table of a update (only in MYSQL 5.7)
I have a update to fix positions values inside a table. The update worked fine in MYSQL 5.5, but after upgrade to MYSQL 5.7 it doesnt work anymore. Now, the "order by" inside the derived table is ...
2
votes
1answer
57 views
PostgreSQL ORDER BY clause effect different number of rows to return in pivot table
I've worked on "Seven Databases in Seven Weeks" recently, and when implementing the the task with building a pivot table using crosstab() - the task was already described here: https://stackoverflow....
0
votes
0answers
25 views
Postresql using subquery select in order by clause
Is it possible to order by a selected columnname?
SELECT * FROM tablex ORDER BY quote_ident((
select column_name from information_schema.columns
where table_name = tablex AND column_name LIKE '...
0
votes
1answer
70 views
Can queries that users ORDER BY using date to sort data be non deterministic?
I have a table in my database where I store dates using the following standard: YYYY-MM-DD. As you can see, I'm not saving the hour. The first time I exported the same table to a csv file I got the ...
0
votes
1answer
108 views
Efficient partition pruning with ORDER BY on same column as PARTITION BY RANGE + LIMIT?
I've set up a table in MariaDB (10.4.5, currently RC) with InnoDB using partitioning by a column of which its value is incrementing-only and new data is always inserted at the end. For this case, ...
0
votes
1answer
27 views
Force index using the unused index columns having effect as order by for the result set?
if I have a index for (col1, col2, col3, col4), my query put col1 and col2 in the query where clause and force MySQL to use the above index. Would my query result be sorted by col3 and col4 ...
1
vote
1answer
30 views
How to order by then start at first non null value and select a limit for number of values
I have an environmental logger and I am trying to select every 5th row starting at where outsidetemp is not null (I want to include the null values after the first not null value). Is there a way to ...
1
vote
1answer
558 views
Optimize MySQL query and index for multiple column range query
I have a table like this:
CREATE TABLE IF NOT EXISTS `jobs` (
`job_id` varchar(36) NOT NULL,
`job_status` varchar(30) NOT NULL,
`created_at` datetime NOT NULL,
`lease_date` datetime,
...
4
votes
6answers
2k views
Only first column in ORDER BY clause is sorted [closed]
It seems like I've misunderstood the concept of 'Order by'.
I have a table with this structure and data:
CREATE TABLE TestTest (Value1 Int, Value2 Int);
INSERT INTO TestTest VALUES
(1, 10),
(2, 9),
...
1
vote
1answer
355 views
first and last record in one query
i want to select first and last score from such data
WITH t AS (
SELECT 1 user_id, '2019-05-15'::date AS created_at, 4 as score
UNION ALL
SELECT 1 user_id, '2019-05-13'::date AS created_at, 12 as ...
2
votes
2answers
276 views
SQL Preserve the Order in group by
I have records like below in my table.
I want distinct records and when I do group by it loses the Order. I want to maintain the order. I have written below query to get the desired however its not ...
1
vote
0answers
28 views
PostgreSQL - SELECT query with ORDER BY and LIMIT 4 very slow, same query much faster with LIMIT >= 40
I've got following sample query from our Postgres DB:
SELECT * FROM advertised_car a0_
LEFT JOIN car_check c1_ ON a0_.car_check_id = c1_.id
WHERE c1_.status = 'ACCEPTED'
ORDER BY a0_.id
LIMIT 4;
...
0
votes
0answers
65 views
postgres: join tables, order by GREATEST
Having the following table structure
table1
id: BIGSERIAL
updated_at: TIMESTAMP WITHOUT TIME ZONE DEFAULT now()
...
CREATE INDEX table1_updated_at ON table2(updated_at);
table2
table1_id: ...
1
vote
1answer
91 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
...
3
votes
5answers
442 views
Order table by two columns
I would like to order this table but I can not find a way to make it work. Can you help me?
I have this table:
I need this:
I have a table where documents are listed, the first field is the id of ...
0
votes
1answer
41 views
Mysql Performance Issue with Group By/Order By
I am using Mysql 5.7 I have table which having 7006500 rows. My query performing group by and fetching row which has maximum count with each group on column which is already indexed but still takes ...
2
votes
2answers
887 views
First order by importance then group by section
In this table scheme http://sqlfiddle.com/#!9/101a9c/9/0
id section rev importance
2 1 b 6
1 1 a 5
3 2 c 9
5 3 e 6
4 ...
2
votes
3answers
78 views
Identify proper index for group and order by date and hour (2 columns)
PostgreSQL 10.4
I have a table that has already an index on date column. Current query plan is doing a Bitmap Heap Scan using the existing date index. I would like to add a new index for this query, ...
0
votes
2answers
69 views
Poor performance on a 'leaderboard' query
Consider the following 2 tables:
players: | rooms:
id | id
name | type
room_id | embed_code_id
points |
My query needs to fetch the 100 players with the most points from a given ...
0
votes
1answer
850 views
postgresql - trouble to order a list obtained by string_agg
I obtained a list of numbers (and text) by a string_agg.
For instance, that :
"09,12,20,200,217,400,500,90,91,92,Exp3"
This is the string_agg:
string_agg(DISTINCT t_ligne.ligne_code::text, ','::...
3
votes
1answer
165 views
Adding an additional “order by” column gives me a much worse plan
in other words, how can I get rid of the sort operator on the picture below?
the picture above shows the execution plan of the following 2 selects together:
SELECT TOP 1 so.OrgType,...
2
votes
1answer
290 views
Conditional ORDER BY based on NULL values in the column
I have a table like this.
table(student_name nvarchar(20) not NULL, grades int).
When I write
select * from table
order by grades asc
It shows this
student_name | grades
---------------------
...
0
votes
2answers
35 views
How to get the best time from submited values?
I have a MySQL table, and i'm trying to figure how can i get the first result for each user.
For example:
Email Time DateSubmission
usera@email.com | 0:14 | 28/2/2019 00:10
usera@...
