CODEWITHSUNDEEP
mysqlsqlselect
Aram
Aram

Reputation: 21

MySQL: how to make an order on select by giving specific score to special records

I have a simple table, for example:

id | name   | category
------------------------
1  | banana | 1
2  | orange | 1
3  | fruit  | 1
4  | potato | 2
5  | car    | 0

I have a lot of special filters that should assign a special score for the list to show. Here is the mechanism for this example:

  1. IF "name" is "banana" or "orange", score = score + 2
  2. IF "name" is "fruit", score = score + 1
  3. IF "name" is "potato", score = score + 0
  4. IF "category" > 0, score = score + 1

So in my results I need to have

id | name   | category | score
-------------------------------
1  | banana | 1        | 3 ( condition 1+4)
2  | orange | 1        | 3 ( condition 1+4)
3  | fruit  | 1        | 2 ( condition 2+4)
4  | potato | 2        | 1 ( condition 3+4)
5  | car    | 0        | 0 ( no match)

And finally I want to order the results by score. I can't create new column, because the conditions are selected by user. What will be MySQL query?

Upvotes: 2

Views: 161

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

You can do this:

SELECT 
  id, 
  name, 
  category, 
  nameCondition + catCondition AS Score
FROM
(
    SELECT
      id, name, category,
      CASE 
        WHEN name IN ('banana', 'orange') THEN 2
        WHEN name = 'fruit'               THEN 1
        WHEN name = 'potato'              THEN 0
        ELSE 0
       END AS nameCondition,
       CASE 
         WHEN  category > 0 THEN 1 
         ELSE 0 
       END AS catCondition
    FROM tablename
) AS t;

SQL Fiddle Demo.

This will give you:

| ID |   NAME | CATEGORY | SCORE |
----------------------------------
|  1 | banana |        1 |     3 |
|  2 | orange |        1 |     3 |
|  3 |  fruit |        1 |     2 |
|  4 | potato |        2 |     1 |
|  5 |    car |        0 |     0 |

Upvotes: 1

raina77ow
raina77ow

Reputation: 106385

One possible approach:

SELECT id, name, category,
       IF(category > 0, 1, 0) + 
       (CASE name WHEN 'banana' THEN 2
                 WHEN 'orange' THEN 2
                 WHEN 'fruit' THEN 1
                 ELSE 0 END)
        AS score
FROM ttt
ORDER BY score DESC

SQL Fiddle

Upvotes: 3

Related Questions