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

learn more… | top users | synonyms

1
vote
0answers
105 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
0answers
320 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
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 ...
0
votes
1answer
95 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
472 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
43 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 ...
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
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
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 ...
0
votes
2answers
172 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. ...
0
votes
1answer
76 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 ...
0
votes
1answer
84 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
2answers
56 views

same field in bothe “where” and “order by” - how to index for this query?

The query: EXPLAIN EXTENDED SELECT *, u.user_id FROM xgate_geoloc g JOIN tig_users u ON g.uid = u.uid WHERE -- corners selection: ( ( ...
0
votes
1answer
57 views

Extract data for all groups in a single SELECT statement

MySQL 5. Table: schedule Columns: id int(10) UN PK AI startdate date groupid smallint(5) UN The following statement extracts the Schedule ID for a given group (the schedule with the latest date ...
0
votes
1answer
60 views

Index on a query with order

I have the following table with >1M rows: CREATE TABLE `wishlist_place` ( `id` int(11) NOT NULL AUTO_INCREMENT, `wishlist_id` int(11) DEFAULT NULL, `place_id` int(11) DEFAULT NULL, `city_id` ...
0
votes
1answer
61 views

Is index like key(timetime) usable when we query: SELECT * … ORDER BY timetime DESC

When we index a timestamp field like timetime. Can we use it for ORDER BY timetime DESC or is it just usable for ORDER BY timetime ASC OR ORDER BY timetime? Does mySQL use this index when doing vise ...
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 ...
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 ...
0
votes
1answer
54 views

Order result by number of occurence of a word in a text

I am working with sqlite3/python . I would like to order results by the number of occurence of %@% in text. For exemple Jimmy 10 // Jimmy wrote 10 strings like %@% James 2 Sophia 0 ..etc The ...
0
votes
1answer
39 views

Sorting by a field in child table

There are two tables: contract (with primary key contractid) and contractupdate (with field contractid referring to a contract). contractupdate also has DATE field startdate and TINYINT field day. I ...
0
votes
1answer
116 views

How to arrange rows selected in MYSQL query?

I'm fetching data from a table "TABLE_A", it's having Primary Key as "PK_ONE" (which is unsigned INT) . I'm fetching data from "TABLE_A" with where clause on PK_ONE ; Ex: SELECT PK_ONE from TABLE_A ...
0
votes
1answer
477 views

Index for ORDER BY on aggregate in MySQL

I'm working with the following query: SELECT *, SUM(entries_count) AS entries_count FROM leads WHERE promo_id IN (1, 2, 3) GROUP BY user_id ORDER BY entries_count DESC LIMIT 25 OFFSET 100; It's ...
0
votes
1answer
803 views

How can I join two tables while filtering the results with a“ WHERE IN” clause? [closed]

What I am trying to do is set up a selection filter for a cascading list on my site. When I say selection filter, I mean I am trying to limit, and sort the results based on what the user selects. I ...
0
votes
1answer
403 views

Postgresql table with one integer column, sorted index, with duplicate primary key

I want to have a table in PostgreSQL with these properties: One column with an integer Allow duplicates in this column. Duplicates will be rare. Duplicates have no meaning logically and will be ...
0
votes
1answer
675 views

Mysql: order by field or select union?

As a newbie, I expected Mysql to return the rows of this query ordered by their ids order in the id clause select * from test where id in (3,1,2) Unfortunatelly for me, that's not true. The results ...
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 ...
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
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) - ...
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 ...
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 ...
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
0answers
382 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
2answers
90 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 ...
0
votes
0answers
32 views

Which of these two queries is better - can be optimized more?

With regards to the usage of the IF statement, which is better to use? Query #1: SELECT whatever FROM auctions AS a LEFT JOIN ............... WHERE ............... ORDER BY ...
0
votes
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 ...
0
votes
0answers
492 views

Using an enum as part of an index definition

When I use an ENUM column in a table the ORDER BY uses the ordinal of the ENUM not its value. I wasn't aware of that. So my question is what are the subtle issues when an ENUM is part of an index ...
0
votes
0answers
180 views

Slow query with ORDER BY with index created (more than 4mins on 0.6million records

I ran into a very weird query performance situation. If I run this query without order by, it finish in millsecs, with order by, it ends up with more than 4mins. EXPLAIN SELECT `book_book`.`id`, ...
-1
votes
2answers
215 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 ...
-1
votes
1answer
40 views

Ordering of rows with different kinds of objects

I have a table of real estate objects. The objects are shown in three lists (sell, rent long, rent short), each object in exactly one list (defined by an ENUM field). I want to add field seq INT to ...
-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
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' ...
-2
votes
1answer
512 views

How to prevent duplicated records when applying ORDER BY NEWID() to fetch them randomly? [closed]

I tried using the solution provided in http://stackoverflow.com/questions/1117761/sql-query-to-return-rows-in-random-order to fetch random records in my query. But I have to add NEWID() to the list of ...