Tsql - How to Define the Sort Order

TSQL - Is it possible to define the sort order?

It's incredibly clunky, but you can use a CASE statement for ordering:

SELECT * FROM Blah 
ORDER BY CASE MyColumn 
    WHEN 'orange' THEN 1 
    WHEN 'apple' THEN 2 
    WHEN 'strawberry' THEN 3 
    END 

Alternately, you can create a secondary table which contains the sort field and a sort order.

TargetValue  SortOrder
orange       1
apple        2
strawberry   3

And join your table onto this new table.

What's the best way to store sort order in SQL?

None of the answers so far have touched on the real problem with custom sort order and that is what happens when two different people want the same records sorted differently.

If you need a custom sort order, you need a related table to store it in, not an additional field. The table would have the userid, the recordId of the data and the sort order for the record. That way Joe Smith can have one order and Sally Jones another for the same data. Now you have the problem of new records being added to the data set. Do you put them at the beginning of the sort order or the end or do you require the person to set an order for them before they can be added to the set. This is in actuality a very complex problem that is generally not worth the amount of time it takes to implement because almost no one ever uses that system once it's in place (I mean do I really want to go through a hundred records and mark the individual order of each one?). Now it gets complicated in terms of saving the order of all the records (which will of course require changes the next time the query is run since there will be new records.) This is very painful process of limited untility.

I did this once in a proposal writing application because we needed to be able to sort the parts and tasks on the proposal in the order we thought would be most impressive to the customer. Even then, we had to institute a default order, so that they only need to move around the two or three things they really wanted to show up first instead of ordering 10,000 individual parts.

A better choice if you can get them to buy off on it, is to allow them to sort the data by columns (desc or asc). Usually the user interface can be designed so that if you click on a column header, it will resort the data by that column. This is relatively straightforward to do and meets most needs for custom ordering.

You really need to discuss this requirement with management and get details of how they want it to work beyond, I want custom ordering. This is often one of those things people think they want, but don't really use.

Create a view with ORDER BY clause

I'm not sure what you think this ORDER BY is accomplishing? Even if you do put ORDER BY in the view in a legal way (e.g. by adding a TOP clause), if you just select from the view, e.g. SELECT * FROM dbo.TopUsersTest; without an ORDER BY clause, SQL Server is free to return the rows in the most efficient way, which won't necessarily match the order you expect. This is because ORDER BY is overloaded, in that it tries to serve two purposes: to sort the results and to dictate which rows to include in TOP. In this case, TOP always wins (though depending on the index chosen to scan the data, you might observe that your order is working as expected - but this is just a coincidence).

In order to accomplish what you want, you need to add your ORDER BY clause to the queries that pull data from the view, not to the code of the view itself.

So your view code should just be:

CREATE VIEW [dbo].[TopUsersTest] 
AS 
  SELECT 
    u.[DisplayName], SUM(a.AnswerMark) AS Marks
  FROM
    dbo.Users_Questions AS uq
    INNER JOIN [dbo].[Users] AS u
      ON u.[UserID] = us.[UserID] 
    INNER JOIN [dbo].[Answers] AS a
      ON a.[AnswerID] = uq.[AnswerID]
    GROUP BY u.[DisplayName];

The ORDER BY is meaningless so should not even be included.

---

To illustrate, using AdventureWorks2012, here is an example:

CREATE VIEW dbo.SillyView
AS
  SELECT TOP 100 PERCENT 
    SalesOrderID, OrderDate, CustomerID , AccountNumber, TotalDue
  FROM Sales.SalesOrderHeader
  ORDER BY CustomerID;
GO

SELECT SalesOrderID, OrderDate, CustomerID, AccountNumber, TotalDue
FROM dbo.SillyView;

Results:

SalesOrderID   OrderDate   CustomerID   AccountNumber   TotalDue
------------   ----------  ----------   --------------  ----------
43659          2005-07-01  29825        10-4020-000676  23153.2339
43660          2005-07-01  29672        10-4020-000117  1457.3288
43661          2005-07-01  29734        10-4020-000442  36865.8012
43662          2005-07-01  29994        10-4020-000227  32474.9324
43663          2005-07-01  29565        10-4020-000510  472.3108

And you can see from the execution plan that the TOP and ORDER BY have been absolutely ignored and optimized away by SQL Server:

There is no TOP operator at all, and no sort. SQL Server has optimized them away completely.

Now, if you change the view to say ORDER BY SalesID, you will then just happen to get the ordering that the view states, but only - as mentioned before - by coincidence.

But if you change your outer query to perform the ORDER BY you wanted:

SELECT SalesOrderID, OrderDate, CustomerID, AccountNumber, TotalDue
FROM dbo.SillyView
ORDER BY CustomerID;

You get the results ordered the way you want:

SalesOrderID   OrderDate   CustomerID   AccountNumber   TotalDue
------------   ----------  ----------   --------------  ----------
43793          2005-07-22  11000        10-4030-011000  3756.989
51522          2007-07-22  11000        10-4030-011000  2587.8769
57418          2007-11-04  11000        10-4030-011000  2770.2682
51493          2007-07-20  11001        10-4030-011001  2674.0227
43767          2005-07-18  11001        10-4030-011001  3729.364

And the plan still has optimized away the TOP/ORDER BY in the view, but a sort is added (at no small cost, mind you) to present the results ordered by CustomerID:

So, moral of the story, do not put ORDER BY in views. Put ORDER BY in the queries that reference them. And if the sorting is expensive, you might consider adding/changing an index to support it.

Using a sort order column in a database table

Update product set order = order+1 where order >= @value changed

Though over time you'll get larger and larger "spaces" in your order but it will still "sort"

This will add 1 to the value being changed and every value after it in one statement, but the above statement is still true. larger and larger "spaces" will form in your order possibly getting to the point of exceeding an INT value.

Alternate solution given desire for no spaces:

Imagine a procedure for: UpdateSortOrder with parameters of @NewOrderVal, @IDToChange,@OriginalOrderVal

Two step process depending if new/old order is moving up or down the sort.

If @NewOrderVal < @OriginalOrderVal --Moving down chain 

--Create space for the movement; no point in changing the original 
    Update product set order = order+1 
    where order BETWEEN @NewOrderVal and @OriginalOrderVal-1;

end if

If @NewOrderVal > @OriginalOrderVal --Moving up chain

--Create space  for the momvement; no point in changing the original  
  Update product set order = order-1 
  where order between @OriginalOrderVal+1 and @NewOrderVal
end if

--Finally update the one we moved to correct value

    update product set order = @newOrderVal where ID=@IDToChange;

Regarding best practice; most environments I've been in typically want something grouped by category and sorted alphabetically or based on "popularity on sale" thus negating the need to provide a user defined sort.

SQL server SORT order does not correspond to ASCII code order

Thanks Ross et al! Found the following documentation which has an excellent explanation on MS SQL Server collation, thought I'd post it here so as to benefit those who comes across this question or related questions.

---

Collation
A collation specifies the bit patterns that represent each character in a data set. Collations also determine the rules that sort and compare data. SQL Server supports storing objects that have different collations in a single database. For non-Unicode columns, the collation setting specifies the code page for the data and which characters can be represented. Data that is moved between non-Unicode columns must be converted from the source code page to the destination code page.

For more details read it here
http://goo.gl/RpBGWN

---

From my code snippet, if I wanted to sort the value in a binary order, the query can be changed to the following:

select c, ASCII(c) ascvalue from testtable order by c collate Latin1_General_BIN


Or change collation definition when creating the table

create table testtable (c nvarchar(1) collate Latin1_General_BIN null)


Which yields the following result:

c | ascvalue
------------
8 | 56
9 | 57
: | 58
; | 59

SQL best practice to deal with default sort order

There is no default sort order. Even if the table has a clustered index, you are not guaranteed to get the results in that order. You must use an order by clause if you want a specific order.

---

Related Topics