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

learn more… | top users | synonyms

0
votes
3answers
40 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
31 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
47 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
74 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
38 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
52 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
16 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
20 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
24 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
67 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
101 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
461 views

How do I 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
55 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
44 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
42 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
34 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
53 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
29 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
2answers
109 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
1answer
318 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
203 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
74 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
32 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
54 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 ...
2
votes
1answer
199 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
75 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` ...
2
votes
2answers
284 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
66 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 ...
-2
votes
1answer
232 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 ...
2
votes
2answers
101 views

Order of returned record with IN statement postgres

I know that the order of returned records is not guaranteed with the IN statement for Pstgres. For example if I do this: SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588) ...
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
1answer
43 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 ...
1
vote
1answer
92 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 | ...
0
votes
3answers
1k 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
1answer
33 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 ...
1
vote
1answer
927 views

Order by alphabet and then by numbers

I have records like A5 A4 Z1 B2 C7 C1A C11A B1 B4 I want them to be sorted in this manner A4 A5 B1 B2 B4 C1 C11A C7 Z1 using the ORDER BY clause. I want them to be sorted by alphabets ...
0
votes
2answers
91 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, ...
0
votes
0answers
266 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
2answers
50 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
0answers
131 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`, ...
0
votes
1answer
272 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
43 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
71 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 ...
2
votes
2answers
3k views

MySQL not using indexes when joining against another table

I have two tables, the first table contains all articles / blog posts within a CMS. Some of these articles may also appear in a magazine, in which case they have a foreign key relationship with ...
0
votes
1answer
229 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 ...
3
votes
1answer
52 views

How is ORDER BY logically Processed

Take this example: SELECT SalesOrderID, CustomerID, SalesPersonID, OrderDate FROM Sales.SalesOrderHeader ORDER BY OrderDate, SalesOrderID DESC OFFSET 0 ROWS FETCH NEXT ...
1
vote
1answer
2k 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
55 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 ...
0
votes
1answer
513 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 ...
2
votes
1answer
1k views

ORDER BY items must appear in the select list […]

Using Microsoft SQL Server 2008, I get the following error. Msg 104, Level 16, State 1, Line 43 ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or ...