CODEWITHSUNDEEP
sqlsql-server
Ankesh Kumar
Ankesh Kumar

Reputation: 525

Find order by based on column value not column name

I have a query where I want to order based on column value not column name i.e.

Suppose I have a column "Description" in table and in my sql I have like condition as

Select * from tableName 
where Description like '%Bombay%' 
or Description like '%Hotel%' like Description like '%Rent%'

I want to order the records if column Description have all the three values or two values or one value i.e. if all the three values are in Description column it should be in at top

Is it possible in sql ?

Upvotes: 0

Views: 52

Answers (1)

Dean Savović
Dean Savović

Reputation: 739

drop table if exists dbo.tableName;


create table dbo.tableName (
ID int primary key
, Description varchar(100)
);

insert into dbo.tableName (ID, Description)
values (1, 'Hotel Bombay Rent')
, (2, 'Hotel Bombay')
, (3, 'Hotel')
, (4, 'Aparment');



select
t.ID
, t.Description
from dbo.tableName t
order by (case when t.Description like '%Hotel%' then 1 else 0 end)
+ (case when t.Description like '%Bombay%' then 1 else 0 end)
+ (case when t.Description like '%Rent%' then 1 else 0 end) desc

Upvotes: 3

Related Questions