test adwords test adwords - 7 months ago 10
SQL Question

Order by the values based on search values

I have a table with 3 columns

id | name | description
------------------------------------------
1 | Service marketing | Description for the marketing
2 | Marketing | Service marketing
3 | Great customer Service | Helpful shows your customers that you really do care about them.


I want to search the values based on 'Service' from both the column name,description and also i need to order the values in asc order by name and description.I am expecting the result set as below.

id | name | description
------------------------------------------
1 | Service marketing | Description for the marketing
3 | Great customer Service | Helpful shows your customers that you really do care about them.
2 | Marketing | Service marketing


I have tried the below queries but not works

SELECT * FROM search where `name` LIKE '%service%'
OR `description` LIKE '%service%'
ORDER BY name,description;


Result for above query

id | name | description
------------------------------------------
3 |Great customer Service | Helpful shows your customers that you really do c...
2 |Marketing | Service marketing
1 |Service marketing | Description for the marketing


Help me on this. Thanks

Answer

If you want ordered by position of 'Service' in the columns you should use this

select position('Service' IN name) as p1, name, position('Service' IN  description) as p2, description 
from my table 
where  position('Service' IN name) != 0 
and position('Service' IN  description) != 0
order by p1 desc, p2 desc;
Comments