New answers tagged order-by
Don't sort by the values that you have, rather have a function that coerces the date strings and then sort by the results of that function. Your result will be one column that contains the data you want, and a second column that contains the sort information: datecolumn datesort 2014-09-04 00:00:00 2014-09-04 B 00:00:00 2014-09-04 00:00:00 ...
Another way: ORDER BY CASE WHEN CONVERT(DATE, col) = col THEN DATEADD(day, 1, col) ELSE col END ;
First order by just the date, then prioritize the values where the timestamp is not midnight over those where it is. ORDER BY CONVERT(DATE, col), CASE WHEN CONVERT(DATE,col) = col THEN 2 ELSE 1 END, CONVERT(TIME, col);
Give them a weight to differentiate between rows with time 00:00:00 and the rest ORDER BY datetimecolumn, CASE WHEN cast(datetimecolumn as time)='00:00:00' THEN 1 ELSE 0 END, cast(datetimecolumn as time)
ANSI SQL only allows the ORDER BY on the outermost query for a variety of reasons, one being what happens when a subselect/view/CTE is joined to another table and the outer query has an ORDER BY itself. SQL server never supported it inside a view (unless you tricked it using a TOP 100 PERCENT which in my opinion is mostly triggering a bug). Even if you ...
If a view was allowed to be sorted then what should be the order of the result here? CREATE VIEW dbo.V1 AS SELECT number FROM SomeTable ORDER BY number ASC GO CREATE VIEW dbo.V2 AS SELECT number FROM SomeTable ORDER BY number DESC GO SELECT * FROM dbo.V1 JOIN dbo.V2 ON V1.number = V2.number
Case is supposed to return one value, not a tuple. Beside, you should compare 1 with something. If you want conditional ordering you can do that with two case statements: Order by case when x = 1 then FirstName else GivenName end , case when x = 1 then GivenName else FirstName end
The planner chooses use of a index depending on the distribution of data in your tables. If you have lots of receivers with only a few arrivals per receiver it will prefer a index like (receiver,arrival), if all arrivals are shared by only a handful of users the (arrival,receiver) index will be much more efficient. Make sure to run ANALYZE on the table ...
A btree index on (sender,arrival) could help. That would allow it to jump directly to the first-arrived message for a given sender. One on (arrival,sender) is less likely to help. That allows you to jump to the first-sent message globally, but then you still have to walk along those messages until you hit one from the specified sender. If that ...
Top 50 recent answers are included