CODEWITHSUNDEEP
sqlmysqlselect
Dirk
Dirk

Reputation: 6884

Mysql "order of operations" Question

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

Upvotes: 2

Views: 2637

Answers (3)

Quassnoi
Quassnoi

Reputation: 425823

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).

Upvotes: 5

Jonathan Leffler
Jonathan Leffler

Reputation: 755064

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.

Upvotes: 0

Ray
Ray

Reputation: 1585

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

Upvotes: -1

Related Questions