Questions tagged [order-by]
A clause used in SQL SELECT statements to sort query results.
0
votes
1answer
14 views
how to select rows which are mapped to a pivot table in a many to many relationship?
I have three tables, locations(Has zipcode, location name), professionals(has name) and locatin_professional. I want to select only those rows from locations table where professional_id is 4. Whats ...
1
vote
1answer
29 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
64 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
20 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
43 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
76 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
427 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
33 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
40 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
68 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
37 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
54 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
161 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
34 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@...
1
vote
2answers
63 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
69 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
33 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
29 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
22 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
44 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
46 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
64 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
53 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
218 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
198 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
100 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
34 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
146 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
60 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
26 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
434 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
52 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
223 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
28 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
92 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
20 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
141 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
35 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
34 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
522 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
45 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
207 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
78 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
322 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
758 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
276 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
------------+------------------------+-----------+-...
