I have a result from an SQL query that I would like to sort alphabetical, but there are a couple of commonly used results that I would like to float to the top. Is there a simple way I can achieve this either by doing clever SQL or by sorting the (asp.net) datatable once I have it filled?
I know the database ID of the things that I want to keep at the top ahead of time if that makes a difference.
You could add an addition field called 'Special'. Special doesn't necessarily have to actually exist in the table, you can derive it the query however you see fit. Then sort as follows:
ORDER BY Special DESC, Name ASC
This of course assumes that your non-special rows would be 0, and the alphabetic field is called 'Name'
You could define a custom column that sets a "special flag" that you can sort by. Then, sort by the alphabetical column.
SELECT ...
IsSpecial = CASE WHEN RowID IN (100,534,203) THEN 1 ELSE 0 END
FROM ...
ORDER BY IsSpecial DESC, RowID ASC, Name ASC
The easiest way to do this is to sort by something that sets those fields aside from the others...
Under the assumption you have access to modify the structure of the table (which you may not), then add a "sticky" field which allows you to mark items as sticky and attach an order to the sticky items if you like. Sort by this field before the regular sort.
As for a trick to do this without that field defined - you would need to sort by some data that you can find in these fields or maintain the ids in a separate table - or a list in your query... not the most ideal way, but it will work.
To add to @EndangeredMassa:
You can also do something similar if you are already doing unions:
SELECT 0 QueryType, Desc, GrandTotal
FROM YourTable
UNION ALL
SELECT 1 QueryType, Desc, LineItems
FROM YourTable
ORDER BY QueryType, Desc
Note: unions may not be ideal in this example...it's just an example!
Just Order by a case statement that uses whatever rules you specify...
...
Order By Case
When [My Rules that designate special]
Then 0 Else 1 End