Skip to main content

Questions tagged [order-by]

A clause used in SQL SELECT statements to sort query results.

Filter by
Sorted by
Tagged with
0 votes
2 answers
1k 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 ...
Kamil's user avatar
  • 101
1 vote
2 answers
3k 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,...
ConanTheGerbil's user avatar
0 votes
1 answer
7k 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 ...
Шариф Латипов's user avatar
0 votes
1 answer
125 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["...
Ellie Chsya's user avatar
0 votes
1 answer
530 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
0 votes
2 answers
146 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-...
beppe9000's user avatar
  • 101
1 vote
1 answer
122 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-...
Dicer's user avatar
  • 113
0 votes
1 answer
26 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....
Jane Lane's user avatar
2 votes
1 answer
2k 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 ...
Paul Draper's user avatar
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
2 answers
82 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 ...
John Bachir's user avatar
7 votes
1 answer
3k 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" ...
Elliot Chance's user avatar
0 votes
1 answer
643 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) ...
Stanislav's user avatar
5 votes
1 answer
2k 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 ...
Dan's user avatar
  • 235
1 vote
1 answer
4k 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 ...
Jatin Seth's user avatar
1 vote
2 answers
1k 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 ...
Adri's user avatar
  • 13
2 votes
2 answers
210 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` ...
Ola's user avatar
  • 21
0 votes
1 answer
3k 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 ...
Abrar Hossain's user avatar
0 votes
2 answers
282 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 ...
user2431082's user avatar
2 votes
1 answer
641 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....
Ivan Sidaruk's user avatar
0 votes
0 answers
42 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 '...
DataLordDev's user avatar
1 vote
1 answer
565 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 ...
olegario's user avatar
  • 131
0 votes
1 answer
1k 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, ...
gertvdijk's user avatar
  • 227
0 votes
1 answer
67 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 ...
Jialun Liu's user avatar
1 vote
1 answer
107 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 ...
Kevin's user avatar
  • 11
1 vote
1 answer
2k 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, ...
Jialun Liu's user avatar
5 votes
6 answers
6k 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), ...
Pantea's user avatar
  • 1,432
1 vote
1 answer
3k 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 ...
user avatar
2 votes
2 answers
4k 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 ...
kkakadiya's user avatar
1 vote
0 answers
46 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; ...
olsavmic's user avatar
0 votes
0 answers
519 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: ...
Jan's user avatar
  • 101
1 vote
1 answer
229 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
3 votes
5 answers
461 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 ...
Liliana del Carmen Castellanos's user avatar
0 votes
1 answer
78 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 ...
5a01d01P's user avatar
  • 135
3 votes
2 answers
9k 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 ...
Joe Doe's user avatar
  • 145
2 votes
3 answers
687 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, ...
Edgar Ortega's user avatar
0 votes
2 answers
109 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 ...
Idan Ptichi's user avatar
0 votes
1 answer
4k 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, ','::...
Cy's's user avatar
  • 1
3 votes
1 answer
170 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,...
Marcello Miorelli's user avatar
2 votes
1 answer
3k 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 --------------------- ...
igelr's user avatar
  • 2,132
0 votes
2 answers
38 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 [email protected] | 0:14 | 28/2/2019 00:10 usera@...
João Carmo Pereira's user avatar
2 votes
2 answers
3k views

How to cast (a) a date value recorded as a VARCHAR to (b) a specific DATE format for (c) use in an ORDER BY clause

How would I cast a column value (a "non-MySQL" DATE string retained as a VARCHAR) to a "MySQL date" so it can be used for sorting? I have dates in the format of 06-02-2019 (6 feb) but MySQL itself ...
Luc H's user avatar
  • 133
0 votes
1 answer
10k views

Is possible to use ORDER BY with IF ELSE condition?

Here I have some data. I want to show the records which have fav=1 at fast, then I want to show the data where the date is greater than the current date in ascending order(date) and at last the ...
Akash Sarkar's user avatar
1 vote
1 answer
631 views

Sort output of plpgsql function

I'm creating a function in Postgres 9.6 that compares two schemas with the same tables. I want to find tables which are empty in one schema but populated in the other. I'm avoiding the reltuples ...
Derek's user avatar
  • 15
0 votes
2 answers
305 views

How to SELECT most common (viral) from a table and LIMIT results for pagination?

Sorry if it's a dubplicate but I can't find an answer to the question. Hello there so I have three tables users and articles and likes, a user can write an article, in the table articles there is ...
Casper X's user avatar
0 votes
1 answer
83 views

Using WITH in sql query

I am trying to replace # with numbers, but the order by is being ignored, and I was told to move the subquery usingWITH or adding a limit to the order by, I tried the latter but it didn't work, and I ...
Mike's user avatar
  • 105
0 votes
3 answers
138 views

What order does Oracle's EXIST condition compare the sub-query results?

I know EXIST works better than IN for larger sub-queries because it checks each sub-query record individually and stops once a comparison is found. Let's say I can order the sub-query in a way to ...
user avatar
2 votes
0 answers
126 views

Postgres / slow query / order by

I'm joining two tables, filter out results by second table and order results by first table's ID. The query is super-slow on Postgres Database. I see the issue is in the ORDER BY statement. But ...
Maksim Karatkou's user avatar
3 votes
1 answer
65 views

How can I get the last items by date desc then by id desc?

I have a employee course table which holds the current record for each of my employees who have some courses that they have taken. Then I have a history that keeps a static version of the save data ...
jwize's user avatar
  • 321
26 votes
6 answers
4k views

How to prove the lack of implicit order in a database?

Recently I was explaining to colleagues the importance of having a column by which to sort data in a database table if it is necessary to do so, for example for chronologically-ordered data. This ...
user avatar

1 2
3
4 5
9