Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [order-by]

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

1
vote
1answer
10 views

Identify proper index for group and order by date and hour (2 columns)

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, no parameters are ...
0
votes
2answers
26 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
0answers
30 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
151 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,...
1
vote
1answer
23 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
34 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@...
1
vote
2answers
46 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 ...
0
votes
1answer
30 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 ...
1
vote
1answer
26 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 ...
0
votes
2answers
27 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 ...
0
votes
0answers
16 views

Pagination using seek-method with nullable columns, sharing a composite index with ORDER BY

I have the following table: CREATE TABLE users( id text PRIMARY KEY, -- actually a uuid organization_id text NOT NULL, lname text NOT NULL, fname text NOT NULL -- other irrelevant fields ); ...
0
votes
1answer
37 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 ...
0
votes
3answers
43 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 ...
2
votes
0answers
61 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 issue is in ORDER BY statement. But increasing ...
2
votes
1answer
52 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 ...
21
votes
6answers
2k 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 ...
0
votes
1answer
67 views

Using MATCH, AGAINST multiple times in MySQL fulltext search

I'm using MySQL database for my e-commerce project. And here is my query for search: SELECT DISTINCT p.id, p.name, p.price, p.created_at, MATCH(p.name) AGAINST('Sony* xperia* FHD* ' IN BOOLEAN ...
4
votes
1answer
58 views

Postgres not smart enough to combine fields in WHERE and ORDER BY for index scan?

I have the following table in Postgres 9.6: Table "public.TagNotifications" Column | Type | Modifiers ...
3
votes
1answer
112 views

SELECT Parent-Child with SORT

I have a simple, one level parent child relation table, with following columns: ID_Asset| Parent_ID_Asset | ProductTitle I need output grouped by Parent followed by children, and also sorted by ...
-4
votes
1answer
80 views

Selecting data from a sub query

I am doing the exercises this is the link (https://www.wiseowl.co.uk/sql/exercises/standard/subqueries/4228/). Can anyone help me. Thanks in advance! Question Write a SELECT statement to return ...
1
vote
1answer
33 views

mysql : ordering a table in the order of the primary key

I have a table with a primary key consisting of two integer columns. I store ~200 millions lines in this table. My program needs to get the full table ordered by the columns of the primary key in the ...
5
votes
2answers
138 views

Is there a “soft” ORDER BY / GROUP BY?

SQL-Fiddle http://sqlfiddle.com/#!9/c82c87b/1 Table Definition First of all, this is my table: CREATE TABLE `stackoverflow` ( `id` VARBINARY( 36 ) NOT NULL COMMENT 'GUID generated by PHP', `time` ...
0
votes
3answers
57 views

How can we optimise this OR and ORDER BY query?

We have a table with the following structure: +----+----------+-------------+---------------------+-----------+---------+----------+ | id | Batch_id | Merchant_id | ExpiryDate | Allocated | ...
0
votes
1answer
24 views

Sort pivot result

I just want to ask how to sort the result of a PIVOT in SQL Server. When I use ORDER BY there is an error: "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, ...
7
votes
4answers
208 views

Adding a ROW_NUMBER() with no column to ORDER BY?

So I'm working on a code golf puzzle and need to add an INT "number" column n to a result while maintaining the current order. Let's say my source data is: SELECT value FROM STRING_SPLIT('one,two,...
1
vote
1answer
32 views

Getting the first 3 children(items) for each owner in a list of primary keys

I have two tables Item an Owner, an a Foreign Key from Item to Owner, owner_id. I'm using PostgreSQL 10. For each owner I want to get the first 3 items if exist. select "items_item"."name" FROM "...
1
vote
3answers
50 views

How correct Postgres plan in case of a double JOIN, ORDER BY and a LIMIT?

I got a SQL request that takes more than 4s: SELECT "Content"."contentId" FROM "UserFeed" JOIN "FeedRole" ON "FeedRole"."feedId" = "UserFeed"."feedId" JOIN "Permission" ON "Permission"."...
1
vote
2answers
122 views

Syntax error in SQL Server while making Dynamic SQL

Scenario: I have a table with 100M+ rows of data so I have created an index with something as: CREATE NONCLUSTERED INDEX FIBaseVoterWithDeletedAt ON dbo.base_voter (name_voter,...
1
vote
0answers
26 views

Exclusive and Inclusive ranking queries in Postgres 9.6

I'm trying to build a filtering/ranking search that would let me order and optionally filter out people based on a 1-to-many relationship with job "focuses". The tables are shown at the bottom. A "...
1
vote
0answers
82 views

ORDER BY doesn't use an index on SQL Server if CASE WHEN is present

Firstly, I apologize - I couldn't create an example SQL Fiddle, because the site doesn't seem to be working right now. Now, onto the problem... I'm getting an inconsistent sort order on SQL Server ...
2
votes
1answer
19 views

column_name ordered same as describe table

I was under impression that default order of columns is internal mysql order but for some reason describe table and selecting column_name gives me compleatly different order. How can I set ORDER BY ...
0
votes
1answer
96 views

Oracle hierarchical query: aggregate over each node's descendants, give results in tree pre-order

Given hierarchical data, for each item I need to get the sum of a column over the sub-tree rooted at the item, and I need the results in pre-order. Example: for the data employee_id | manager_id | ...
-1
votes
1answer
27 views

Count comma seperated values in mysql ORDER BY clause

I have a table name products: id - pro_name - ip - date_created 1 - abc1 - 27.106.121.23,27.106.121.86 - 2018-08-15 13:32:00 2 - ...
2
votes
1answer
33 views

C++ stoi equivalent in SQL Server's T-SQL

I'm looking for something I can use in Microsot SQL Server that would act the same way the stoi function in C++ acts. Specifically, I'm looking to be able to grab numerics at the beginning of the ...
0
votes
1answer
371 views

oracle sql: How to not select select duplicate records from table

Can any help me to fix the below sql query SELECT DISTINCT ORDER_NUMBER, FLAG FROM TABLE Below was the result Order_number FLAG LP-13288 false LP-13288 true LP-13292 false LP-13290 ...
2
votes
1answer
44 views

sort results by highest performing country & region

I want to check which country & regions are performing the best in sales. I have the following order table: | order_id | amount | |----------|--------| | 100 | 1000 | | 101 | 250 | ...
0
votes
2answers
154 views

MySQL Very Slow Performance Using Order by Clause

I have a one table with millions of entry.Below is table structure. CREATE TABLE `useractivity` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `userid` bigint(20) NOT NULL, `likes` bigint(20) DEFAULT ...
0
votes
3answers
43 views

Difference of two columns with possible NULL cells

I need to sort a table by the difference of two columns as SELECT (first - second) AS dif FROM table1 ORDER BY dif DESC but the two columns can be NULL. In this case, it returns the error: ...
2
votes
1answer
240 views

Optimze performance for LIMIT / OFFEST on collected result of multiple queries

I have large tables in Postgres 10.4 (many millions rows) and three piped SELECT queries. The percentage of rows contributed to the total result for each SELECT is roughly: SELECT 1 : ~5 % rows ...
6
votes
2answers
476 views

How to apply ORDER BY and LIMIT in combination with an aggregate function?

A fiddle for my question can be found on https://dbfiddle.uk/?rdbms=postgres_10&fiddle=3cd9335fa07565960c1837aa65143685. I have a simple table layout: class person: belongs to a class I want to ...
2
votes
2answers
237 views

Why is this query with WHERE, ORDER BY and LIMIT so slow?

Given this table posts_lists: Table "public.posts_lists" Column | Type | Collation | Nullable | Default ------------+------------------------+-----------+-...
2
votes
2answers
78 views

In what direction SQL Server WHERE clause conditions are checked?

I have this query: SELECT RCodeID as 'ListItemID', RCode as 'ListItemName' FROM RCode WHERE RCode not like '%,%' AND ISNUMERIC(RCode) = 1 AND CAST(RCode as int) >= CAST(@...
0
votes
1answer
179 views

Unable to apply order by clause on distinct select in Oracle

The query is: select distinct( Name ) from Students where marks>75 order by Substr(Name,-3,3),ID asc; This shows an error message: order by Substr(Name,-3,3),ID asc * ERROR at line 4: ORA-...
2
votes
1answer
180 views

How to remove duplicates from any array and preserve ordering in PostgreSQL?

I'm looking for a way to eliminate duplicates from a PostgreSQL array while preserving the ordering of its elements. What I currently have are the following functions: create function array_unique( ...
0
votes
2answers
64 views

Get unique ids based on order by another column using group by with row_number

Here is a link to the SQLfiddle. For pagination purpose, I am first executing a query to execute if's and order by's and return a list of ids, using which I query for all the data with contained lists ...
2
votes
2answers
693 views

GROUP BY needs ORDER BY NULL to avoid filesort

I've noticed again and again that when I EXPLAIN queries with a GROUP BY clause, I get filesort as an extra condition. A long time ago I read a suggestion to use GROUP BY NULL in these cases to avoid ...
2
votes
1answer
461 views

Optimize a trigram search with custom sort order

I'm new to typeahead / full text searching but I'm trying to figure out an algorithm that yields the best results for my search use-case. The additional module pg_trgm is installed. I have a table ...
-1
votes
1answer
254 views

Order by a different field on a GROUP_CONCAT

Given the following script: SELECT category AS MODULE, acl_roles.name as ROLE_NAME ,GROUP_CONCAT( CASE aclaccess WHEN 89 THEN 'E' WHEN 0 THEN 'N' ...
2
votes
1answer
47 views

Querying an ordered list with OR or IN clause

I am querying items from a database and display it to the user. (in my example I'll use contacts in table tContacts. The user selects a couple of items in an order he decides (the user can random ...
2
votes
2answers
38 views

MySQL ORDER BY id, but keep identical names next to each other

Suppose I have the following table: id name --------- 1 lion 2 tiger 3 lion 4 bear 5 tiger 6 bear I would like to be able to return results with the most recent id column first, but ...