CODEWITHSUNDEEP
sqlsql-servert-sql
Leo Loki
Leo Loki

Reputation: 2575

How to order by 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 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

Upvotes: 13

Views: 25902

Answers (3)

Ken Clark
Ken Clark

Reputation: 2530

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

Upvotes: 2

ljh
ljh

Reputation: 2594


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

Upvotes: 2

John Woo
John Woo

Reputation: 263723

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

Upvotes: 31

Related Questions