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

learn more… | top users | synonyms

1
vote
2answers
65 views

How to order by alphanumeric codes in SQL Server [on hold]

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
102 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
79 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
21 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
65 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
39 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 ...
2
votes
1answer
40 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
43 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
56 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
38 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
153 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
164 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
15 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
565 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
47 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
34 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
53 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
18 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
679 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
48 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
34 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
108 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
56 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
62 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
148 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
205 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
52 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 ...
3
votes
1answer
243 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
22 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
68 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 ...
0
votes
2answers
105 views

Group by + Order by Behavior Explanation? [duplicate]

Suppose I have: Product(_id,Name) Provider(_id,Name) Provides(ProductId, ProviderId, Price) If I want to obtain the providers who sell the most expensive products, and whose name starts with A. ...
2
votes
2answers
276 views

SQL order by query results in any arbitrary (but reproducible) manner

For testing purposes I need to get some data from a set of database tables in any arbitrary (but reproducible) order. The idea being that I can later compare two runs using a textual diff tool. Is ...
2
votes
3answers
517 views

How to ORDER BY typical software release versions like X.Y.Z?

Given a "SoftwareReleases" table: | id | version | | 1 | 0.9 | | 2 | 1.0 | | 3 | 0.9.1 | | 4 | 1.1 | | 5 | 0.9.9 | | 6 | 0.9.10 | How do I produce this output? | id | ...
1
vote
1answer
73 views

How to optimize multiple ORDER BYs?

I'm getting sluggish performance on a table with ~300,000 rows and B-Trees on each column. This is for a dynamic pagination page where the query is constructed on demand, and the application caches ...
0
votes
1answer
62 views

PostgreSQL : inconsistent result when using WHERE clause on VIEW

This is my VIEW declaration CREATE OR REPLACE VIEW inv_groups_vw AS SELECT gg.id, gg.sku, gg.name, gg.description, CASE WHEN count(i.*) = 0 THEN '[]'::json ...
2
votes
1answer
71 views

Making MySQL use primary index without hinting or forcing

I have the following db structure: CREATE TABLE `question` ( `id` int(11) NOT NULL AUTO_INCREMENT, `level_id` int(11) NOT NULL, `video_id` int(11) NOT NULL, `is_active` tinyint(1) NOT NULL, ...
0
votes
1answer
61 views

Unsure of Rank / Dense_Rank - Mulitple Columns - change rank when unique combination changes

I need to get the unique combinations of 2 fields, with a RankID for each unique combination In this example I need to create new IDs for each Customer/Value combination. Source table: CustID ...
0
votes
0answers
82 views

Mysql Show records according to their positions by grouping and order by

I am writing a query i should show the records according to the rank position given along with price order and group by sitename shopdetails id | productid | productname | sitename | siteid | ...
1
vote
1answer
30 views

cant order NULL value properly (mysql)

So I got this view in mysql : CREATE VIEW vote_question_totals AS SELECT question_id , SUM((vote_question_type)='1') question_total_plus , SUM((vote_question_type)='-1') question_total_minus , ...
2
votes
2answers
334 views

In PostgreSQL is there a way to rank/weigh columns so as to order the results?

Let's say I am searching through three text columns. I don't just want results that match a given word or phrase, but I also want to weigh them (column A > column B > column C) - thereby ...
3
votes
1answer
1k views

Why is the ORDER BY clause in a view ignored as soon as it's called with a WHERE claue?

I have a view that joins several tables, just to simplify a frequently called query. This view has an ORDER BY clause on a calculated field (the order will never be different). (on SQL Server Express ...
0
votes
0answers
340 views

group by and order by making query very slow in mysql?

Query works fast: select SQL_NO_CACHE users.id, users.name from useres left join user_statistics on users.id = user_statistics.user_id where users.is_deleted = 0 group by users.id ...
0
votes
1answer
88 views

ORDER BY that doesn't contain repeating values

Relevant SQL Fiddle (postgres 9.2) How do I state a SELECT .. ORDER BY .. that avoids having repeated values for a column? That is, for rows of the column "rank" (1,3,3,1) how do I get a result so ...
1
vote
1answer
36 views

Am I using the ORDER BY clause incorrectly

I am trying to enumerate a database, created specifically for the purpose of learning SQL injections. I am trying to discover the number of columns a particular select statement might be using. I do ...
0
votes
2answers
74 views

Sorting of results is slow some times but fast at other times

I have a mysql query that is joining a few tables and then uses order by field for sorting. It sometimes runs quite fast at other times it is slow. While it was slow i profiled the query to find that ...
3
votes
1answer
844 views

How do I sort the results of a recursive query in an expanded tree-like fashion?

Let's assume you have a nodes tables like this: CREATE TABLE nodes ( node serial PRIMARY KEY, parent integer NULL REFERENCES nodes(node), ts timestamp NOT NULL DEFAULT now() ); It ...
1
vote
0answers
96 views

MySQL query with order by decreased performance

Here is my schema: CREATE TABLE `UserMst` ( `UserID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `EMailID` varchar(50) DEFAULT NULL, `UserName` varchar(20) DEFAULT NULL, `FullName` ...
3
votes
2answers
1k views

MySQL: Is it possible to order a query by a specific letter using ORDER BY?

What I'd like to do is order my query so that the results show every row found, but are ordered by showing results that begin with the letter 'D' first. Is this something that is possible to do with ...
-1
votes
2answers
151 views

Fetching Recently added rows from table [closed]

I have the following table structure , name || description || timestamp varchar varchar date time stamp column consists of time the row is added Now i need to fetch the ...