A clause used in SQL SELECT statements to sort query results.
18
votes
4answers
1k views
Why does ORDER BY not belong in a View?
I understand that you cannot have ORDER BY in a view. (At least in SQL Server 2012 I am working with)
I also understand that the "correct" way of sorting a view is by putting an ORDER BY around the ...
9
votes
1answer
3k views
Order by column should have index or not?
I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How ...
8
votes
3answers
796 views
What are the alternatives for an ORDER BY clause in a View?
This question just had to be in this site :)
ORDER BY is forbidden to use in a view, as I understood because of the possibility for multiple order by's when using this view.
I know that there are ...
7
votes
5answers
1k views
Oracle sort varchar2 column with special characters last
How can I sort in Oracle a Varchar2 or NVarchar2 column to be in my own custom defined order. Or are any existing options available that will put letters first, then numbers, then all special ...
7
votes
4answers
2k views
How do I do a complex GROUP BY in MySQL?
I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to be able to group rows together that have an equal key, but I don't want ...
7
votes
2answers
233 views
Must an index cover all selected columns for it to be used for ORDER BY?
Over at SO, someone recently asked Why isn't ORDER BY using the index?
The situation involved a simple InnoDB table in MySQL comprising three columns and 10k rows. One of the columns, an integer, ...
6
votes
3answers
114 views
Arbitrarily ordering records in a table
A common need when using a database is to access records in order. For example, if I have a blog, I want to be able to reorder my blog posts in arbitrary order. These entries often have lots of ...
6
votes
1answer
104 views
Parent-Child Tree Hierarchical ORDER
I have to following data in SQL Server 2008 R2. SQLFiddle
Schema:
CREATE TABLE [dbo].[ICFilters](
[ICFilterID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [int] NOT NULL DEFAULT 0,
...
6
votes
1answer
451 views
adding an order by to this query returns faster than without, why?
Any ideas on why adding a sort to this query returns considerably faster than without the order by? I would expect the opposite so what could make this happen?
SELECT TOP (500) ...
5
votes
1answer
150 views
How does SQL Server order results when joins are used?
How does SQL Server figure out the order of the records in the result set of query execution?
I am trying to make heads or tails of it but find myself scratching my head. When I change the fields I ...
5
votes
3answers
604 views
Query best possible matches and order them
I'm trying to write a query along these lines:
select *
from tbl
where
col1 = 1
and col2 = 2
and col3 = 3
order by
...
;
I want first all the results where all 3 WHERE ...
4
votes
2answers
421 views
Is SELECT ROW_NUMBER() guaranteed to return results sorted by the generated row numbers?
E.g. consider the SQL query:
SELECT
A.[Name],
ROW_NUMBER() OVER(ORDER BY A.[Name] ASC)
FROM
[FooTable] AS A
Here I observe the results being returned sorted by A.[Name]. If I change the ...
3
votes
4answers
1k views
MySQL : Conditional ORDER BY to only one column
Hello and thanks for taking time to read this question.
I am using MySQL, and I want to sort results using ORDER BY to one specific column, but the results must be ordered according an specific ...
3
votes
3answers
629 views
Does ORDER BY on a clustered key effect performance?
Say I have a table clustered on PrimaryKey, and in all cases I want my results to be ordered by PrimaryKey so I additionally always ORDER BY PrimaryKey in all queries.
Does this ORDER BY affect ...
3
votes
2answers
3k views
CASE ORDER BY with multiple columns, and different sort options
I am attempting to use a T-SQL CASE ORDER BY in a stored procedure where I am passed @OrderBy parameter as a TINYINT.
@Orderby = 1 Then Date column should be ASC
@Orderby = 2 Then Date column ...
3
votes
3answers
1k views
Want to order on two columns but not the usual way
I have a table as below
ID userID Date
1 2273 22/08/2011
2 2274 24/08/2011
3 2275 26/08/2011
4 2273 26/08/2011
5 2273 26/08/2011
6 2271 26/08/2011
And want result as ...
3
votes
2answers
2k views
MySql: Delete all rows greater than n entries ordered by datetime
Can someone help me with a MySql query to delete all rows greater than n entries ordered by date?
I.e. say I have 1200 rows of data with a timestamp column. I need to order it by date and preserve ...
3
votes
1answer
43 views
Sequential joining of tables in order
Table albums has (among other field) field id.
Table photos has column id, field album which is a foreign key referring to album id and some other fields (which are irrelevant for the question I ...
3
votes
1answer
50 views
Special ordering
I have a hierarchical query but I don't manage to order it as I want.
I have a column named sequence that indicates how to order it but the data is kinda mixed up and I cant do it properly.
This a ...
3
votes
3answers
367 views
MySQL: ORDER BY useless?
CREATE TEMPORARY TABLE tt;
INSERT INTO tt; many times
SELECT FROM tt ORDER BY id LIMIT ?,1000;
The temp table is created at the beginning of the script having an "id" auto_increment column (and ...
3
votes
1answer
75 views
Bayesian ranking
I have the following table in my database:
╔════════╦═════════════╦═══════════════╗
║ Name ║ total_stars ║ total_reviews ║
╠════════╬═════════════╬═══════════════╣
║ Item A ║ 27 ║ ...
3
votes
1answer
2k views
MySQL: Optimize UNION with “ORDER BY” in inner queries
I just set up a logging system which consists of multiple tables with the same layout.
There is one table for each data source.
For the log viewer, I want to
UNION all the log tables,
filter them ...
3
votes
1answer
330 views
How to order query results by tag weight in IN('tag1', 'tag2', 'tag3')
Consider a simple query as
SELECT * FROM posts
JOIN tags USING(post_id)
JOIN tag_map USING(tag_id)
WHERE tags.tag IN('tag1', 'tag2', 'tag3')
tag_map is a many-to-many relationship between tables ...
3
votes
1answer
2k views
How do I make MySQL auto increment in random order?
I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to create a new column for each key that would be an integer such that ...
2
votes
2answers
691 views
Sort by match of LIKE
I wondering how I can implement SQL to get results sorted by best match of a like predicate. I have 100K articles in the database and when user call some items by part of name. I want to show the ...
2
votes
3answers
140 views
Ordering by date and other columns
I have a one table Q&A like below:
CREATE TABLE `totaalconceptterreinonderhoud`.`acties`
(
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`type` int(11) NOT NULL DEFAULT '3' ...
2
votes
3answers
1k views
How to make conditional ordering for two or more columns
IN MSSQL 2005 I am writing one query with conditional sort and my problem is I do not know how I can sort condidional using two columns.
if i wrte code like this it is working normaly
select
*
from
...
2
votes
2answers
124 views
Slow MySQL query with Order and Group by
I have a query that is taking a long time to execute (1.13s). The two tables user_articles and articles have around 25,000 records. This is the query:
SELECT `user_articles`.*
FROM `user_articles`
...
2
votes
2answers
99 views
Migrating SQL Server 2000 to SQL Server 2008
I'm migrating SQL Server 2000 to SQL Server 2008. I have many views. How do I check each views that if they have an order by clause?
Thanks.
2
votes
2answers
123 views
MySQL order by - one column conditional on another column
In MySQL I have a table (simplified for this question)
CREATE TABLE `projects` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`priority` int(11) DEFAULT '1000000',
...
2
votes
2answers
127 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
59 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 ...
1
vote
1answer
97 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
1answer
76 views
Should all queries where you expect a specific order, include an ORDER BY clause?
In order to better understand the SQL Server query processor, I've been thinking about how the ORDER BY clause works, and how SQL Server provides results.
It appears SQL Server will provide results ...
1
vote
2answers
80 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
1answer
63 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
32 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
226 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
1answer
200 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
239 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
...
1
vote
1answer
56 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
180 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 ...
1
vote
1answer
455 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
1k 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
180 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
4answers
76 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, ...
0
votes
1answer
41 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
34 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 ...
0
votes
1answer
40 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
51 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 ...
