SQL Server Performance Forum – Threads Archive
Unique SortHi, I would like to sort my Select statement with a unique sort, meaning I would like to put the data in a certain sort order neither ascending or decending.
The challenge is not to create another column for the sort number. How can I do that?
Example. I would like to put the data such as
1233 where the sort is how I want it to be.
Without creating another column, I think it can accomoplish only this by not creating any index on the table…
If you want to deliberately mess up the order of the data in the resultset, you can use: ORDER BY NEWID() Please note that if you do not include an ORDER BY clause, the order of the resultset is already (by definition) unpredictable. It may look like there is a certain order, probably by the PK, but this is by accident. In larger resultsets, you should notice that the non-ordered results are indeed not in PK order – and if they are, then this is purely by accident. Also, it looks like you may have a list of FK values, referring to a substitute key. Check the order of the corresponding natural key on the RK table – perhaps LocName: LocID LocName
1233 D In that case, add your Location table to the query, and order by the LocName column, instead of the LocID. If you still have to order by the LocId according to this arbitrary list, then use a CASE expression that maps each of the LocID values to a number: ORDER BY CASE LocID WHEN 10 THEN 1 WHEN 19231 THEN 2 WHEN 213 THEN 3 WHEN 1233 THEN 4 END There may be cleverer and funnier calculus tricks that I’m overlooking.