Fine control over SQL Sort Order

Question

I have a SQL Report driven by a query that sorts it in ascending order of some numerical value. But the user wants two particular rows of data, which happen to appear at different ordinal positions in the query results, to be juxtaposed. Is there a way to do this, either through the report's driving SQL or the .rdl file itself?

Answer 1

It sounds like you're asking for this:

select * from table where col1 = first_val or col1 = second_val;

where first_val and second_val are the values for the rows that the user wants returned.

If that's not what you're looking for, please clarify your question.

Answer 2

Just add another calculated expression as the first order by Expression, which puts those two values ahead of all others...

Select [Other stuff]
From Table
Order By Case colName 
          When first_val then 0
          When second_val then 0
          else 1 End,
      colName

or, EDIT (to include @astander's suggestion)

Select [Other stuff]
From Table
Order By Case 
         When colName In (first_Val, second_Val)
         Then 0 else 1 End,
      colName

and another Edit, to put second_val immediately after first_Val...

Select [Other stuff]
From Table
Order By Case 
         When colName < first_Val And colName <> secondVal Then 0
         When colName = first_Val Then 1
         When colName = secondVal Then 2 
         Else 3 End,
      colName

Answer 3

Also, CURSORs are a good tool for fine-grained control. I'm using that, but I will see if it can be stripped of iterative code by using the ORDER BY CASE suggestion.