How to order by an arbitrary condition in SQL
15,550
Solution 1
SELECT *
FROM Bable
ORDER BY CASE WHEN name LIKE '%$..' THEN 0 ELSE 1 END,
Name
Solution 2
select * from Bable
order by charindex('$',name,0) desc, name asc
SQL Fiddle Demo
Solution 3
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
Author by
Leo Loki
Updated on June 25, 2022Comments
-
Leo Loki about 2 years
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?