Hot answers tagged order-by
17
(Indexed views aside, of course.)
A view is not materialized - the data isn't stored, so how could it be sorted? A view is kind of like a stored procedure that just contains a SELECT with no parameters... it doesn't hold data, it just holds the definition of the query. Since different references to the view could need data sorted in different ways, the way ...
17
Absolutely not. Proof:
SELECT
A.[Name],
ROW_NUMBER() OVER(ORDER BY A.[Name] ASC),
ROW_NUMBER() OVER(ORDER BY A.[Name] DESC)
FROM
[FooTable] AS A
The only way to guarantee an order in SQL is to ask for it, use ORDER BY on the result itself.
15
Since you haven't told SQL Server how to order the results, it is free to do so in whatever is the most efficient. In this way it will depend on what is the cheapest to sort, and the columns you select will drive that because it in turn depends on the cheapest index(es) to use to get at the information requested by the query. This can change from execution ...
8
It isn't possible to calculate relevance with the LIKE predicate. For SQL Server (which from previous questions I believe is your platform?) you'll want to look at full-text search which supports scoring/ranking results by relevance.
8
Only the outermost ORDER BY will guarantee order
Any intermediate or internal ORDER BY is ignored.This includes ORDER BY in a view
There is no implied order in any table
There is no implied order from any index (clustered or not) on that table
Links
"Sorting Rows with ORDER BY" (MSDN)
ORDER BY guarantees a sorted result only for the outermost ...
8
If you had asked the question I think you actually meant to ask:
How can I order by ROW_NUMBER() without repeating the complex ORDER BY expression?
We could have told you to create an alias for the ROW_NUMBER() expression, and then sort using the alias:
SELECT
A.[Name],
rn = ROW_NUMBER() OVER (ORDER BY <complex expression>)
FROM
...
7
Yes, MySQL can use an index on the columns in the ORDER BY (under certain conditions). However, MySQL cannot use an index for mixed ASC,DESC order by (SELECT * FROM foo ORDER BY bar ASC, pants DESC). Sharing your query and CREATE TABLE statement would help us answer your question more specifically.
For hints on how to optimize ORDER BY clauses:
...
6
Is it indeed necessary for all selected columns to be indexed in order for MySQL to choose to use the index?
This is a loaded question because there are factors that determine whether an index is worth using.
FACTOR #1
For any given index, what is the key population? In other words, what is the cardinality (distinct count) of all tuples recorded in ...
6
If I want to move record 0 to the start, I have to reorder every record
No, there's a simpler way.
update your_table
set order = -1
where id = 0;
If I want to insert a new record in the middle, I have to reorder every record after it
That's true, unless you use a data type that supports "between" values. Float and numeric types allow you to ...
6
I had a simpler repro in mind:
CREATE TABLE #x(z CHAR(1));
CREATE TABLE #y(z CHAR(1));
INSERT #x SELECT 'O';
INSERT #x SELECT 'R';
INSERT #x SELECT 'D';
INSERT #y SELECT 'E';
INSERT #y SELECT 'R';
SELECT z FROM #x
UNION ALL
SELECT z FROM #y;
Results:
O
R
D
E
R
Now add an index:
CREATE CLUSTERED INDEX z ON #x(z);
SELECT z FROM #x
UNION ALL
SELECT ...
4
SELECT* FROM mytable ORDER BY
LOCATE(CONCAT('.',`group`,'.'),'.9.7.6.10.8.5.');
I took your sample data, loaded it into a table called mytable and ran it.
Here are the results:
mysql> use test
Database changed
mysql> drop table if exists mytable;
Query OK, 0 rows affected (0.04 sec)
mysql> create table mytable
-> (
-> names ...
4
SELECT *
FROM Mytable
ORDER BY
userID, Date
I assume Date is really a date/time type and not varchar...
Edit, after clarification:
Untested
SELECT
M.*
FROM
( --one row for each user
SELECT MIN(Date) AS FirstUserDate, userID
FROM MyTable
GROUP BY userID
) foo
JOIN
MyTable M ON foo.userID = M.userID
ORDER BY
...
4
What about doing a little math against your ID column to dynamically generate the group?
SELECT grp, FLOOR(id/10) AS id_grp
FROM animals
GROUP BY grp, id_grp
This would give you groups of 10 based on the ID of the record. I used your animals table above to generate the data below.
Sample data
INSERT INTO animals VALUES
...
4
SELECT col1,
col2,
col3,
col4,
col5,
col6
FROM TableX
WHERE col1 = 1
OR col2 = 2
OR col3 = 3
ORDER BY (CASE WHEN col1 = 1 THEN 1 ELSE 0 END) +
(CASE WHEN col2 = 2 THEN 1 ELSE 0 END) +
(CASE WHEN col3 = 3 THEN 1 ELSE 0 END) DESC,
col4, col5, col6
or, for MS-Access:
ORDER BY ...
4
It almost certainly will affect performance.
If you just do a query like
Select *
From Table
Order by PrimaryKey
It likely won't affect anything at all.
Bear in mind, though, that this only determines the order of the rows at the leaf level of the clustered index. If you do JOINs, or use other indexes that avoid key lookups, then the ORDER BY will ...
4
Since all your matches have to match the LIKE pattern in order to be included, the simple thing to do is assume that shorter values for the column you're matching are "better" matches, as they're closer to the exact value of the pattern.
ORDER BY LEN(item_nale) ASC
Alternatively, you could assume that values in which the match pattern appear earlier are ...
4
Along with JNK's answer, you could also consider:
DECLARE @Example TABLE
(
first_name NVARCHAR(50) NOT NULL,
last_name NVARCHAR(50) NOT NULL,
cert_end_date DATE NOT NULL,
other_columns NCHAR(100) NOT NULL DEFAULT (N'')
UNIQUE (cert_end_date ASC, first_name, last_name),
UNIQUE (cert_end_date DESC, first_name, ...
4
Some options:
Persist the sorted version of your data to a table via trigger, and use it.
Use Oracle Locale Builder to build a custom sort order. (Caveat: I have never used this, so I do not know what gotchas may exist there.) You could then use the NLSSORT function with that custom sort order.
4
one possibility is to avoid conflicting sorts- if the view is sorting by one order and the select on that view is sorting by another order (not being aware of the view sort), there may be performance hit. So it is safer to leave the sorting requirement to the user.
another reason, sort comes with a performance cost, so why penalizing all users of the view, ...
4
If you're asking how to get this information from a SQL Server 2008 installation, then use sys.sql_modules to find the text of a procedure/view
select m.definition
from sys.views v
join sys.sql_modules m
on v.object_id = m.object_id
where definition like '%ORDER[ ]BY%'
or
select object_definition(object_id)
from sys.views v
...
4
It would be helpful if you could post the actual query you're running. Even better would be if you created a SQL Fiddle so that we can play around with your data and your tables.
It appears, however, that you simply want to replace your ORDER BY clause with
ORDER SIBLINGS BY sequence
As an example, if you want to report on the EMP table and list the ...
4
I'm not sure why bunches wouldn't be its own table (I think that would make this problem easier), but I'll run with it as is. If I'm understanding the problem correctly, assuming you wanted bunch 1, something like this should do the trick:
SELECT p.image
FROM photos p
JOIN album a
ON p.album = a.id
WHERE a.bunch = 1
ORDER BY a.id, a.seq, p.id
3
If the sort order that you want to specify is already supported by Oracle, you can do this by ordering by the NLSSORT function - like so:
ORDER BY NLSSORT(sorted_column, 'NLS_SORT = XDanish') -- Replace XDanish as appropriate
You can find a list of supported sort orders here.
3
Break it out a little more:
ORDER BY CASE WHEN @orderby = 1 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) END ASC,
CASE WHEN @orderby = 2 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) END DESC,
tp.lastname ASC,
tp.firstname ASC
You only need the sort order to change on the first field, so don't enclose the others in the CASE.
It ...
3
It would be something like the following. Please verify the syntax and functionality as I have not tested it. There might be a better way to do this.
SELECT * FROM
(
SELECT
@rownum1 := @rownum1 + 1 AS rownum,
acties.*
FROM
acties
INNER JOIN (SELECT @rownum1 := 0) AS rownum
WHERE
type = 1
ORDER BY
...
3
Use MySQL's find_in_set() function to do this. It is more concise but less portable than the CASE approach gbn proposed.
For example:
SELECT `names`, `group`
FROM my_table
WHERE `group` IN (9,7,6,10,8,5)
ORDER BY find_in_set(`group`,'9,7,6,10,8,5');
Because it relies on string searching, find_in_set() is useful mainly for ordering on small sets of ...
3
Typically (it's asked daily in SO) you'd use a CASE which is standard SQL
ORDER BY
CASE group
WHEN 9 THEN 1
WHEN 7 THEN 2
WHEN 6 THEN 3
WHEN 10 THEN 4
WHEN 8 THEN 5
WHEN 5 THEN 6
ELSE 7
END
I'd be interested to see how this compares over a large dataset to the LOCATE(CONCAT...) ...
3
Unless I'm grossly misunderstanding your structure, a simple join should work fine:
SELECT stories.vid, body, timestamp, COUNT(votes.id) votecnt FROM stories
INNER JOIN votes ON stories.vid=votes.vid
WHERE stories.lv=1 AND stories.status=1
GROUP BY votes.vid
ORDER BY votecnt DESC
I'm not sure what you mean by
It would be even better, if the first ...
3
The most obvious cause would be that (select max(ID) from tblR where cID = c.cID and ISNULL(aField,'') <> '') sub-query in the WHERE clause in combination with the TOP 500 is making the order make a difference.
In either case it will probably be running that sub-query individually for every row it might otherwise return until it has found 500 that ...
3
For a complex query, SQL Server may decide that it needs to sort portions of data in your query, and may even require multiple sort operators based on which indexes are available and the complexity of your query.
These sorts can definitely impact the performance of your query especially if you are working with large tables, and in some cases you'll find ...
Only top voted, non community-wiki answers of a minimum length are eligible
