MS SQL Order BY Case

Question

I have a query with 3 columns (for sake of the example). One is DATE and one is [Type] and one is PRODUCT. I need to order the query by DATE but also by type in terms that name like Buy, Transfer In shows up first and then Transfer Out and Sell. The order of [Type] is very important for me since there is this case where I get Sell even before I see Buy for same product...

SELECT [PRODUCT], [DATE], [TYPE]
FROM table_name
ORDER BY [DATE] ASC

Is there a way to order it like that. The names aren't necessary alphabetical order.

For example:

Washer , 20100506, Sell
Washer , 20100506, Buy

And i need that in order Buy, then Sell. But keep in mind that Buy and Sell are just examples and it won't be named like that so alphabetical sorting isn't reallly gonna help here. I would like to be able to define the order of Type if it's possible.

Answer 1

You mean like this:

SELECT ProductID, Name FROM Production.Product
WHERE Name LIKE 'Lock Washer%'
ORDER BY ProductID ;

Or this:

SELECT ProductID, Name FROM Production.Product
WHERE Name LIKE 'Lock Washer%'
ORDER BY ProductID DESC;

MSDN

Answer 2

If you only have a few you could put the logic in a case statement

SELECT [DATE], [TYPE]
FROM table_name
ORDER BY [DATE] ASC, 
   CASE WHEN [Type]  IN (`Buy`,'TransferIn') Then 0 Else 1 End ASC

Otherwise you may be better off creating a table, view, or TVF that maps types and sort orders that you can then join on.