CODEWITHSUNDEEP
phpmysql
user1320260
user1320260

Reputation:

Order results based on SQL query

I currently query a table for items with a certain category. I have extended this query to also looks within the item description column too. My question is, how do i order results so that the category matches are first and the description is last?

`SELECT * FROM myTable WHERE `category` LIKE '%keyword%' || `description` LIKE '%keyword%'

In my above query simply adding a ORDER BY category will not suffice. The only way I can think of doing this is two separate queries with two separate result variables.

I'm using php and mysqli

Upvotes: 0

Views: 50

Answers (2)

Jii
Jii

Reputation: 7

It could be done like this

SELECT *
FROM myTable
WHERE `category` LIKE '%keyword%' || `description` LIKE '%keyword%'
ORDER BY
    CASE
      WHEN `category` LIKE '%keyword%' THEN 1
      ELSE 0
    END CASE
      DESC

If you would like to broaden that SQL query and preserve these ordering, you can utilize:

SELECT *
FROM myTable
WHERE `category` LIKE '%keyword%' || `description` LIKE '%keyword%'
ORDER BY
    CASE
      WHEN `category` LIKE '%keyword%' THEN 1
      ELSE 0
    END CASE
      DESC,
    CASE
      WHEN `description` LIKE '%keyword%' THEN 1
      ELSE 0
    END CASE
      DESC

Results, what are both description results and category results, will be on begin - as it was not specified, I suppose, that so it is requested or accepted.

Upvotes: 0

Chop
Chop

Reputation: 534

It could be done like this

SELECT *
FROM myTable
WHERE `category` LIKE '%keyword%' || `description` LIKE '%keyword%'
ORDER BY
    CASE
      WHEN `category` LIKE '%keyword%' THEN 1
      WHEN `description` LIKE '%keyword%' THEN 0
END

Upvotes: 1

Related Questions