Geek asks and answers

How to order an arbitrary condition in SQL

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 modify this query to return results ordered so that first names with names containing "$" are displayed, and then those that do not, each group being sorted namein ascending order?

Table structure here

+5
source share
3 answers
SELECT *
FROM   Bable
ORDER  BY CASE WHEN name LIKE '%$..' THEN 0 ELSE 1 END,
          Name
+14
source

You can do the same with charatindex

SELECT * FROM Bable WHERE about = 'texttexttexttext'
Order by Case When CHARINDEX('$',name)>0 Then 0 Else 1 End,name
+2
source

<to> select * from Bable order by charindex('$',name,0) desc, name asc SQL Fiddle Demo

+2
source

More articles:


All Articles