How can I sort data using an 'order by' sql clause with specified data at the beginning

Question

I currently have a table(/grid) of data that I can page, filter and sort. On the table I also have a built in checkbox column. Paging, filtering and sorting right now happen within the SQL query. What I want to be able to do is sort by the clicked items in my checkbox column. This would bring all items that are checked to the front of the table. Since the checkboxes themselves are all client side I can't just tell the SQL query to sort by a column that doesn't exist (maybe I need to dynamically create one?)

In essence what I think would happen is that the checked boxes ID value would be sent into the query the the SQL query itself would somehow know to sort by that first and then by the others specified.

Something like, where 1, 2, and 3 are clicked:

SELECT * FROM Blah ORDER BY (SELECT ID FROM Blah WHERE ID IN (1,2,3)), AnotherColumnToSort

That's the plan anyway, anyone have any ideas on how to actually accomplish that?

Update: Smack I'm on an Oracle DB and not SQL Server like I had thought.

Answer 1

Set a BIT field to 1 when the row is checked, 0 otherwise, then order by that field first:

SELECT *
FROM Blah
ORDER BY Checked DESC, AnotherColumnToSort

If you don't want to (or can't) store whether the row is checked in the table, derive it using a CASE clause:

SELECT *
FROM Blah
ORDER BY
  CASE
    WHEN ID IN (1, 2, 3) THEN 1
    ELSE 0
  END DESC,
  AnotherColumnToSort

Answer 2

I would avoid re-querying the data you already have -- it's not going to scale well and you will only have problems with it. This type of functionality is available in a lot of 3rd-party grids (I would venture that damn near all the pay-for grids have this).