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 ...
