Questions tagged [order-by]
A clause used in SQL SELECT statements to sort query results.
0
votes
1answer
31 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
55 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
75 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
68 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
29 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
134 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
56 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
23 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
152 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
28 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
48 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
87 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
22 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
71 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
18 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
53 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
23 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
32 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
214 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
99 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
33 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
134 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
...
5
votes
2answers
297 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
196 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
117 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
104 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
62 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
495 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
323 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
153 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
43 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
37 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 ...
1
vote
2answers
273 views
“Column does not exist” with INNER JOIN and MAX query in ORDER BY
I'm using PostgreSQL 9.6 with ActiveRecord. I'm trying to get all customers sorted by a specific provider. The relationship between models is: customer has many orders, which belong to a provider.
...
-3
votes
1answer
33 views
Ordering an aggregated query
I have a table consisting of a pk, an id, an integer value and a type consisting ~160,000,000 rows.
Consider these data in the table:
CREATE TABLE foo(pk,id,value,type)
AS VALUES
( '1' , '1', '...
7
votes
2answers
273 views
User-Defined ORDERing in SQL [duplicate]
I have just read an interesting blog article regarding User-Defined resultset ordering.
Some applications, such as todo lists, need to maintain a user-defined
order of items. The challenge is ...
5
votes
1answer
485 views
ORDER BY gives ERROR: function array_position(text[], character varying) does not exist
I have a pretty basic categorical column in a Postgres database that is currently stored as VARCHAR. I can select a count of each with:
I though adding an ORDER BY array_position() would do it:
...
4
votes
2answers
1k views
How to use a conditional ORDER BY in MySQL?
I have the table courses where we have some fields and also the field course(string), favorited(bool), favoritedDate(dateTime), dateUpload.
The teacher uploads the course and this date is stored in ...
3
votes
1answer
39 views
Sort table by column with column ordered by earliest instance of another column
Order a table query based on a column but using another column.
I have a table equivalent to the following:
FOO | BAR | TIMESTAMP
-------------------------
CAT | BOB | 2018-03-01
CAT | ANN ...
2
votes
2answers
1k views
ORDER BY clause kills query performance
Context:
PostgreSQL 10, with 3667438 records in users table, the users table has a JSONB called social, we usually use a strategy of indexing computed function outputs, so we can aggregate ...
1
vote
1answer
43 views
Order BY Customized - Char by Char
My target is to sort text columns by a custom sorting.
e.g., ADBC instead of the usual ABCD.
With this custom ordering, rows would be ordered by in this way:
ADAGIO
ABACUS
DICE
BEE
Because D comes ...
1
vote
3answers
870 views
MySQL order by slow
I have a table named 'customer' with the following structure and indexes:
DESCRIBE customer;
+--------------------+---------------+------+-----+-------------------+-----------------------------+
| ...
0
votes
1answer
385 views
group by, order by and pick last value of a column in each group [closed]
I am using PostgreSQL 9.6. I have one "events" table. I need to group by an "item_id" column then order by "date" column in each group and then pick last value of "value_num" column in each group. Pls,...
1
vote
1answer
203 views
Oracle pagination with order by field not unique performance
Usually, we will write pagination SQL like this if order by field is unique:
SELECT *
FROM (
SELECT XX.*, ROWNUM AS RN
FROM (
SELECT *
FROM T_LOG
WHERE OP_TYPE = 'Q' ...
0
votes
1answer
30 views
Select all values in same group using value in group
I have following query:
SELECT
SubstitutionGroupID,
Material.Number,
Material.DescriptionEN
FROM
MaterialSubstitution
INNER JOIN Material on Material.MaterialID = ...
3
votes
1answer
309 views
inconsistent distinct + order by behavior between mysql 5.6 and 5.7
I use MySQL 5.6 and 5.7 on Ubuntu 16.04.3 LTS and I have a problem with the following query (which is derived from drupal 7 but have simplified it in order to pinpoint the issue):
select distinct ...
6
votes
2answers
1k views
Postgres slow query with order by, index and limit
I am trying to improve the performance of a postgres(9.6) query. Here is my schema and table contains about 60 million rows.
Column | Type | Modifiers
-------...
4
votes
1answer
395 views
Sorting multiple columns in a full outer join, possibly containing NULLs
I am trying to compare a plan against a history to see if there are steps omitted or if unplanned steps have been included. The regular, planned history items as well as the anomalies are intended for ...
2
votes
2answers
106 views
Sum by Implicit Group
I'm sure I'm missing an obvious solution on this, but I'm trying to summarize values for groups that are defined by an explicit group number and an implicit ordering. I'm sure that doesn't make this ...
