Mysql "order of operations" Question

Question

Hi all,

Here's my situation: I want to SELECT all entries from a database WHERE id = $id. But I want the results to be listed in a certain priority. If, criteria = $criteria, then I want those results displayed first. Otherwise, I just want to keep displaying the rest of the rows.

My question is this: will this solve my problem?

SELECT field1 WHERE (criteria=$criteria AND id = $id) OR id=$id  LIMIT 5

Will the query look at the () first? If not, is there another way to do this without splitting this into two separate queries?

Thanks,
Michael

Answer 1

SELECT  field1
FROM    mytable
WHERE   id = $id
ORDER BY
        criteria = $criteria DESC, id
LIMIT 5

, or this:

SELECT  *
FROM    (
        SELECT  field1
        FROM    mytable
        WHERE   id = $id
                AND criteria = $criteria
        ORDER BY
                id
        LIMIT 5
        ) q
UNION
FROM    (
        SELECT  field1
        FROM    mytable
        WHERE   id = $id
        ORDER BY
                id
        LIMIT 5
        ) q2
ORDER BY
        criteria = $criteria DESC, id
LIMIT 5

The latter is more efficient if you have an index on (id, criteria) (in this order).

Answer 2

Not related to the original question, just to clarify: operator AND have higher priority than OR hence why a or b and c or d is equal to a or (b and c) or d

Answer 3

SELECT field1
  FROM mytable
 WHERE id = $id
 ORDER BY CASE WHEN criteria = $criteria THEN 0 ELSE 1 END CASE
 LIMIT 5;

This will list rows matching the criteria before those not because zero comes before one.

If this won't work directly - as written - then put the info into a sub-query:

SELECT field1
  FROM (SELECT field1,
               CASE WHEN criteria = $criteria THEN 0 ELSE 1 END CASE AS ordering
          FROM mytable
         WHERE id = $id
       ) AS subqry
 ORDER BY ordering
 LIMIT 5;

Often, there will be other secondary criteria to determine the order of rows within the 0 vs 1 ordering.