How to order by an arbitrary condition in SQL

sql, sql-server, t-sql

Solution

SELECT *
FROM   Bable
ORDER  BY CASE WHEN name LIKE '%$..' THEN 0 ELSE 1 END,
          Name

- SQLFiddle Demo

Problem

I have the following table: ``` CREATE TABLE Bable ( id int identity primary key, name varchar(20), about varchar(30) ); INSERT INTO Bable (name,about) VALUES ('ООО Name Firm 1','texttexttexttext'), ('ООО Name Firm 2','texttexttexttext'), ('ООО Name Firm 3','texttexttexttext'), ('ООО Name Firm 4','texttexttexttext'), ('ООО Name Firm 5','texttexttexttext'), ('ООО Name Firm $1','texttexttexttext'), ('ООО Name Firm $2','texttexttexttext'), ('ООО Name Firm $3','texttexttexttext'), ('ООО Name Firm 6','texttexttexttext'), ('ООО Name Firm 7','texttexttexttext') ``` And I can write a query like the following: ``` SELECT * FROM Bable WHERE about = 'texttexttexttext' ``` How can I alter this query to return results ordered such that those with names containing "$" appear first, followed by those that do not, with each group then ordered by `name` ascending? Structure of the table is here

Original source