Stack Exchange Network

Stack Exchange network consists of 174 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.

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