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

learn more… | top users | synonyms

2
votes
1answer
60 views

MySQL - Ordering 'featured' items first

I need to have the results of a query show featured items first for a given date before listing the day's other items. You can ignore the categories that are being pulled from another table. ...
0
votes
3answers
27 views

I want a select to give me id's that are not in a consecutively order

I have an Oracle db with a table like this: +----+-------+ | ID | INDEX | +----+-------+ | 1 | 0 | | 1 | 1 | | 2 | 4 | | 2 | 5 | | 2 | 6 | | 3 | 0 | | 3 | 1 | | 3 ...
2
votes
1answer
20 views

sorting output by incrementing IP in mysql

I want an incrementing list of IP #'s in a subnet to display on a php web page in a table. Right now, 10.1.2.3 would display after 10.1.2.10 in the sequence. My current query is this: SELECT * FROM ...
2
votes
3answers
83 views

Change the Row Order to alphabetical then numeral

I have a table with a username as unique & primary key. I ran this Query to order them in ascending order ALTER TABLE `table` ORDER BY `Username` ; So the rows are ordered like this ...
1
vote
2answers
50 views

How does ORDER BY FIELD() in MySQL work internally

I understand how ORDER BY clause works and how the FIELD() function works. What i want to understand is how the both of them work together to sort. How are the rows retrieved and how is the sort ...
4
votes
4answers
340 views

Why index is not used with order by?

I trying to get info about why MySQL does not using my index when I create a inner join and trying to ORDER BY on the end. I have my SQL query here: SELECT * FROM product p INNER ...
1
vote
2answers
37 views

Apply limit and conditions per grouped data

I am building an api endpoint to send statistics data that then is going to be plotted on a graph. The hardest part is being the query I need to execute. To sum up I want the first n most sold ...
1
vote
0answers
19 views

How to sort new data for display? [migrated]

I am new to SQL. I have a table called Categories with 3 columns: id int(25) category varchar(25) subcategory varchar(25) I would like to add a new subcategory "Real Estate Agency" in the category ...
-2
votes
1answer
71 views

Sorting in dd/mm/yyyy format group by date

select CONVERT(nvarchar(15),TransDate,103) as TransDate, isnull(SUM(Convert(int,Amount)),'0') as Amount from HMS_DiagnosisTransactions where TransDate between '05/01/2015' and '07/24/2015' ...
0
votes
1answer
44 views

Set in mySQL a predefined order for SELECT queries without using the ORDER BY clause

I have a MySQL database and users access it through an old legacy software (OLS) the company bought years ago. OLS connects to MySQL through MySQL ODBC driver. There is no way to change OLS in recent ...
1
vote
3answers
98 views

Get rows changed between two dates (latest and previous)

I have a table _sync_history with this data: +----+----------------+---------------------+ | id | object_name | sync_time | +----+----------------+---------------------+ | 1 | territory ...
1
vote
2answers
63 views

Get the nth distinct column value ordered by another column

I want to get the row with the nth distinct value in a column, as ordered by another column. In more real terms, I want the 1000th distinct email_address as ordered by created_at, and the ...
2
votes
1answer
83 views

Avoid joining to the same table multiple times

I have two tables: CREATE TABLE one ( id int4 primary key, p_id int4, k_id int4, c_id int4 ); CREATE TABLE two( id int4 primary key, p_id int4, k_id int4, t_id int4, pos int4 ); Table ...
2
votes
1answer
80 views

Why is ORDER BY LIMIT 1 so much faster than MAX?

Here are my two queries: select sts_in from sta_session where sts_user_id=2006 AND sts_sessid!='0jitkt80gg3avere03tqk4lhi6' order by sts_in desc limit 1; And select max(sts_in) from ...
1
vote
2answers
27 views

Custom ORDER BY

Are there other ways to use ORDER on my results? 20150507094958A DISPOSED 2015-05-07 09:50:00.853 Samuel MONEY 20150507094958A DISPOSED 2015-05-22 09:25:00.000 Jeff AUTOMOBILES ...
2
votes
1answer
54 views

MySQL Filesort/Order By Slow

I've been struggling with a query for the past few weeks and it now just appears as gobbly-gook to me when I try to optimize. The query runs - at about 2.3 seconds, but I know there has to be a more ...
2
votes
0answers
32 views

How to order by fields of joined table while selecting for update?

For selecting some rows of table Fwt_Chipkarte for update I use the following SQL statement (IBM DB2): SELECT o.Archiviert, o.Id, o.IdComm, o.Kartennummer, o.Konto, o.LetzteAenderung, ...
5
votes
2answers
715 views

List of all missing order numbers that are not in the table

Our workplace has started to scan all documents rather than manually keep them in archives etc. On average we are scanning 1,000 documents per time (this happens about 30 times per day). Each time ...
1
vote
2answers
96 views

How to order by alphanumeric codes in SQL Server [closed]

I have a table with some project codes. These codes are e.g.: B001, B002, Z15001, Z14001, P003,... I need this result table: Z15001 Z15002 ... B001 B002 ... C001 C002 ... Z14099 Z14098 Z14097 ... ...
2
votes
4answers
164 views

Mysql Order By affecting a Query with column alias, group by, and having

Description: I have a query with column alias, group by and having. It's getting altered when I add an order by. This strange behavior is only happening in 5.6.16 and 5.6.21 (which I was using in ...
0
votes
3answers
104 views

Adding order by to a query is slowing down the query even after Indexing

While joining columns with huge data even after indexing the column order by is slowing down the query. Without order by query is executing within 2 secs. But with order by query is taking 145 secs. ...
0
votes
0answers
37 views

Performance hits with MySQL Left Joins and Order By

There are a lot of questions out there regarding this issue, but most answers seem to be very case-specific. I've tried to make this general so it'll help others too. The Issue We have a smallish ...
1
vote
1answer
125 views

Reverse auto_increment id existing column in phpMyAdmin

I have imported csv file through phpMyAdmin into one table. I have set auto-increment field, as shown below, but of course it ordered it ascending: +----+------------+------+ | id | date | name ...
1
vote
2answers
68 views

mysql - order by first condition in where clause

I have a table consists of the following fields id, name with the following values 1, ciro 2, test ciro 3, ciprox 4, other 5, other i would like to get all the values that begin with "ci" or ...
3
votes
1answer
125 views

Does Postgres preserve insertion order of records?

For example when I'm using query which returns record ids INSERT INTO projects(name) VALUES (name1), (name2), (name3) returning id; Which produce output: 1 2 3 Will this ids point to ...
1
vote
1answer
64 views

MySQL Update a table based on the last data from another table while grouping with conditions

I need a query to UPDATE a series of fields based on another table Table lp_plates_backup | uid | brand | model | | 1 | | | | a | Old | Error | ...
2
votes
1answer
86 views

Operator “~<~” uses varchar_pattern_ops index while normal ORDER BY clause doesn't?

Let's say I have a table with 1 000 000 records. Structure of table is: create table individual ( id serial primary key, surname varchar(128), "name" varchar(128), patronymic ...
0
votes
1answer
45 views

How to sort query filename output like Windows explorer?

i would like to order my query output the way Windows Explorer does. First special characters like _, then the numbers, and the letters last. _ttt.bmp 0.gif bbb.png
3
votes
1answer
286 views

Unused index in range of dates query

I have a query that is not using existing indices and I do not understand why. The table: mustang=# \d+ bss.amplifier_saturation Table ...
1
vote
1answer
478 views

Cassandra row ordering best practice

I have a column family that store articles. I'll need to get those articles from the most recent to the oldest, getting them from Country, and of course the ability to limit the number of fetched ...
1
vote
0answers
19 views

Custom cycling order in MySQL

Having following Foo table: id | name | group ===|=========|====== 1 | "John" | 1 2 | "Paul" | 1 3 | "Marry" | 1 4 | "Josh" | 1 5 | "Mike" | 2 6 | "Kevin" | 2 7 | "Ramon" | 2 8 | "Kim" ...
7
votes
2answers
744 views

Does DB2 order data by the primary key

I've recently had a discussion with a colleague who was pushing to remove order by clauses from a production query because the order by column was the same as the primary key. After a lengthy ...
1
vote
0answers
71 views

PostgreSQL query slow when using tsvector and ORDER BY despite having index

Running on a large database (around 1.5G) The following query takes about 70 seconds to run, SELECT x FROM DBText x WHERE x.status = 2 AND x.publishedTime < 4574590406397 AND x.service = ...
0
votes
1answer
44 views

MySQL ORDER SELECT based on a score

I have a table 'member' and want to be able to search the table and order results by relevance. I have a single string to search on and want to search multiple fields. Example, given_name and ...
2
votes
0answers
73 views

Schema for user drag and drop ordering

I'm trying to work out the schema for a system where users will be able to order items in an (unlimited length) list by drag and drop. Is there any kind of standard way of storing this ordering ...
0
votes
1answer
32 views

Filter and then sort products by availability

I have table with products. Each product can be avaible in 1+ suppliers, so product can be present multiple times. products - id_productSupplier (primary key) - id_product (foreign key) - ...
3
votes
2answers
1k views

Database Implementations of ORDER BY in a Subquery

I am using an application (MapServer - http://mapserver.org/) that wraps SQL statements, so that the ORDER BY statement is in the inner query. E.g. SELECT * FROM ( SELECT ID, GEOM, Name ...
0
votes
0answers
51 views

Slow MySQL Query when Ordering

This query takes around 2 seconds when the order by is included, without the order it takes less than a tenth of a second. The field that is doing the sorting, qc_products.addedDate, is an index and ...
-1
votes
1answer
37 views

List two columns - MYSQL [closed]

i have a table like this: cp1 | cp2 ----------- A | 5 A | 1 A | 1 B | 3 C | 4 and i want to create a query like this: cp1 | cp2 ----------- A | 5 A | 1 ...
2
votes
1answer
122 views

MySQL: Why queries with subqueries are much faster than single query?

I made few queries on my db lately First query: SELECT `table`.* FROM (`table`) WHERE table.id in(27172172,[...bunch of ids...],27171770) ORDER BY field (`table`.`id`, ...
0
votes
3answers
65 views

how to order a field based on two parts of the row value

I have a field with contents of the form 22/4, 30/8, 30/5, 30/3 I want it ordered in the form 22/4, 30/3, 30/5, 30/8 with the part after / ordered if the part before / is same! Please help!!
0
votes
1answer
95 views

How to order by Levenshtein distance?

I want to order a table according to a char field using Levenshtein distance or similar because some entries may contain underscores or grammar errors. It is a table with all the books I plan to read ...
0
votes
1answer
259 views

Duplicate records on joining

I have two tables books and books_data. books can have multiple books_data, books_data can have only one book. books_data table has date column by which I want to sort the books table. When trying to ...
1
vote
1answer
333 views

Oracle - WHY do an ORDER BY for INSERTs AS SELECT?

I have an application that was migrated from MSSQL to Oracle 11G. It needs re-design but that's outside the scope of the project. In short, the application gets legacy data, builds a parent table ...
0
votes
1answer
63 views

Order by one column inside other column

I've a MySQL table that looks like the following: http://sqlfiddle.com/#!2/ca67ac/1 I use it to build a grid where i put my content. The resultant is this: I've to render every row separatelly ...
6
votes
1answer
413 views

Order by bug on SQL Server 2005 vs SQL Server 2012

I have a query generated by entity framework that returns wrong results on a SQL Server 2005 database (Microsoft SQL Server 2005 - 9.00.5000.00 (X64)) but good on SQL Server 2012 (Microsoft SQL Server ...
3
votes
1answer
416 views

Using an index for both asc and desc on a string column

I know a trick to make order by queries faster when we want to order by asc and desc. For integers store a negated value. So if I store -7,-11,-8,-5,-1,-2 the order by asc will give -11, -8, -7, 5, ...
1
vote
1answer
19 views

Find the number of where statements that matched in postgresQL

Right now I have a query that looks something like this SELECT id FROM products WHERE products.details->'make' = ? OR products.details->'model' = ? OR products.details->'year' = ...
0
votes
1answer
28 views

MySQL order of query

My query return like this. 12 14 14 99 78 78 54 I want result to be sorted so that no same number comes consecutively in the result. The result that I want is like this. 12 14 99 14 78 54 78
0
votes
1answer
84 views

Mysql - MyISAM Default order

We are using Mysql V5.6. We have a MyISAM table. when we open the table, the records will be displayed in some order instead of primary key order. Even when selecting the records will not be retrieved ...