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

learn more… | top users | synonyms

2
votes
2answers
400 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 ...
2
votes
2answers
163 views

Order by sub select

I have 3 tables: articles, paragraphs, notes An article may have many paragraphs, and a paragraph may have 0 or 1 note. Now I want to find all notes belong to an article: select * from notes where ...
2
votes
1answer
68 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 ...
2
votes
1answer
94 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, ...
2
votes
1answer
1k views

SQL Server 2005: Full-text search, order by matches

Is it possible to do a full-text search in SQL Server 2005, ordering on the number of matches? For example, let's say I have the following products for sale. Tennis Racquets Tennis Balls Footballs ...
2
votes
1answer
59 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
50 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
1answer
75 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 ...
2
votes
1answer
998 views

Why is MySQL order by performance poor within a single partition of a partitioned table?

I have to store some sequence numbered data in MySQL. I have about 300,000 data items per day for about a 10 year span. Let's say the table structure is just sequence number (a big int) and data (a ...
2
votes
1answer
2k views

MySql: #1221 - Incorrect usage of UNION and ORDER BY

I have the following query which is intended to retrieve the newest 3 comments for each post in an array of posts. SELECT * FROM comment AS tbl WHERE sscm_id IN ( SELECT sscm_id FROM comment AS t1 ...
2
votes
0answers
30 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, ...
2
votes
0answers
63 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 ...
2
votes
1answer
118 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`, ...
1
vote
1answer
332 views

weird sql server behavior when inserting and selecting

Today after one of the drives on our sql server ran out of space. The drive contained eventracker logs, so I didn't think it was a big deal, but then I noticed when I insert a record into a database, ...
1
vote
3answers
85 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
101 views

Special Oracle Order

I have a table (emp) with two columns in ORACLE: COMPANY OFFICE EMP ------------------------ 9999 00001 emp1 9999 00001 emp2 9999 00002 emp3 9999 00002 emp4 ...
1
vote
2answers
80 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 ... ...
1
vote
2answers
52 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 ...
1
vote
2answers
165 views

Filesort while using primary key for orderby

I have been breaking my head for the past two days now for this. CREATE TABLE `clients` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `guarantor_name` varchar(50) NOT NULL, ...
1
vote
4answers
954 views

MySql Order by isnull() Performance Problem

my sql below is use for listing stock added from 10 days ago. Order by isnull(Price) is use so that stock without any price yet will still being listed. AddDate and Price has an index. SELECT Id, ...
1
vote
1answer
67 views

How to order by 5, 501, 50101, 6, 601, 60101, etc?

I've got a list of ledger account numbers. These numbers go like this: 5 (main account, #5) 501 (sub account of main account 5, #01) 50101 (sub account of this account, #01) etc. So if I have a ...
1
vote
2answers
26 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 ...
1
vote
1answer
85 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 ...
1
vote
1answer
58 views

MySQL Grouping Order

Is it possible to force the order in which a result set is grouped? I have a table with multiple languages in it, and I'm doing a GROUP_CONCAT to get a comma separated list of the product name in each ...
1
vote
2answers
42 views

Order by on an alphanumerical column

I have a column that has strings of the following type: Miller 10 Allen 20 King 10 .... Jones 100 I try to sort the column based on the numerical part of the data. I tried the following: SELECT ...
1
vote
1answer
558 views

Use order from another select clause

So what I'm actually trying to achieve is filter the most voted on stories. The stories are saved in one table, and all of the votes in another one. I have this statement to get the vid column order ...
1
vote
2answers
60 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 ...
1
vote
1answer
55 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 | ...
1
vote
1answer
290 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 ...
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' = ...
1
vote
1answer
2k views

db INDEX on ORDER BY FIELD in very simple query does not help - still slow response?

I have very simple query which returns 200K records very slow (20 seconds). SELECT * FROM TABLE ORDER BY ID DESC If I do just SELECT * FROM TABLE it returns quick result. I created INDEX on ...
1
vote
1answer
95 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
1answer
347 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
1answer
34 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 , ...
1
vote
1answer
139 views

increment row value does not work properly with where condition

I have created sql fiddle as below it is working fine if i remove where condition(WHERE rg.groupid= 1) output without where condition | ID | RANK | MYORDER | PREVIOUSRANK | ...
1
vote
1answer
4k views

SQL Limit + count group?

I'm tracking all the visitors of my site using this table: Visitors table In my admin panel, I have an overview of which countries visited my site, where they came from, ..., but because over 20+ ...
1
vote
1answer
71 views

ORDER BY optimization issue

I have a query: SELECT user_details.id , user_details.first_name , user_details.last_name, user_accounts.name account_name, jtl0.account_id account_id, user_details.title , ...
1
vote
1answer
280 views

GROUP_CONCAT with ORDER BY , but results are not ordered

The following query works fine SELECT *, (SELECT GROUP_CONCAT(url SEPARATOR '$$' ) FROM project_photos WHERE project_id = projects.id ORDER BY priority) AS images FROM projects WHERE catID = 2 LIMIT ...
1
vote
1answer
2k views

Slow complex query with group/order

I have a problem with a very important query in my application. It is very slow when adding group and order to it. So the full query is: SELECT m.id, m.title, m.title_text, ...
1
vote
1answer
2k views

Help optimizing MySQL slow query

i'm running a wordpress which needs some mysql optimization, i have a slow query and i would like to get rid of "Using temporary; Using filesort" query: EXPLAIN SELECT SQL_CALC_FOUND_ROWS wp_posts.* ...
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" ...
1
vote
0answers
63 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 = ...
1
vote
1answer
37 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 ...
1
vote
0answers
103 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` ...
1
vote
3answers
3k views

Running an “ORDER BY CASE” query on MySQL view breaks it afterwards

Whenever I create a MySQL view and then run a query ordering the results with ORDER BY CASE WHEN (...), the view becomes unusable. The newly created view works properly and all SQL is correct. This ...
1
vote
0answers
314 views

MySQL GROUP BY and ORDER BY giving inconsistent results

I'm currently working with WordPress, and I've added a new meta value which is a number between 0 and 30. Zero is ignored and up to 30 need to be organised in order and only the latest (based on the ...
0
votes
1answer
91 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 ...
0
votes
1answer
445 views

Getting MySQL User Variable in Sequence, While Using ORDER BY RAND()

I am running a SELECT statement, which ultimately will be in an INSERT..SELECT statement. I want a result like this ID COUNTER 3 1 7 2 1 3 20 4 12 ...
0
votes
1answer
1k views

Optimizing ORDER BY for simple MySQL query

I'm trying to optimize this really simple query, and it gives me grief for a day now :( Seems pretty straightforward, I need to select with a JOIN from 2 tables, and get top X results sorted in a ...
0
votes
1answer
33 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 ...