CODEWITHSUNDEEP
mysqlsql
jeremykrall
jeremykrall

Reputation: 181

Ordering SQL results by the field where they are found

We have a search query that allows the users to enter a string and then searches 3 separate fields via LIKE '%string%'. The fields are:

When then return the results and right now just order them arbitrarily. We would prefer the results be ordered first by results found in the Name field, second by items found in the Description field, and then lastly in the Type field. Because we have paging and sorting also associated with this, we really want it returned in one record set. Is this even possible?

Thanks in advance.

Upvotes: 1

Views: 58

Answers (3)

narendra
narendra

Reputation: 21

You can try this:

Select * 
from Tablename 
where Name like '%string%' 
and description like '%string%' 
and type like '%string%' 
order by name,description,type

Upvotes: 0

user2001117
user2001117

Reputation: 3777

Please try this :

Select * 
from Table 
where Name like '%string%' 
and description like '%string%' 
and type like '%string%' 
order by name,description,type

Upvotes: 0

Andriy M
Andriy M

Reputation: 77737

In MySQL, the following should work:

SELECT *
FROM atable
WHERE Name        LIKE '%string%'
   OR Description LIKE '%string%'
   OR Type        LIKE '%string%'
ORDER BY
   CASE
     WHEN Name        LIKE '%string%' THEN 1
     WHEN Description LIKE '%string%' THEN 2
     WHEN Type        LIKE '%string%' THEN 3
   END
;

Upvotes: 1

Related Questions