Mysql "order of operations" Question

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

Quassnoi · Accepted Answer · 2009-08-04 19:42:40Z

5

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

edited Aug 4, 2009 at 19:42

answered Aug 4, 2009 at 19:30

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Powerlord Over a year ago

+1: This is how I'd do it. Strangely, you can't do this sort of thing in Oracle without a subquery because Oracle's way of doing it (rownumber) is applied before the order by.

Dirk Over a year ago

Does the location of DESC matter? I want the criteria first, in terms of highest id to lowest id. Should it be criteria = $criteria, id DESC instead?

Quassnoi Over a year ago

@Michael: if you want highest id's first, use ORDER BY criteria = $criteria DESC, id DESC

Dirk Over a year ago

Thanks, then what does the DESC in criteria = $criteria DESC do as opposed to criteria = $criteria

Quassnoi Over a year ago

@Michael: criteria = $criteria DESC is a boolean expression. TRUE are 1 and FALSE are 0. DESC makes TRUE values come first.

|

Jonathan Leffler · Accepted Answer · 2009-08-04 19:38:08Z

0

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.

answered Aug 4, 2009 at 19:38

Jonathan Leffler

759k145 gold badges961 silver badges1.3k bronze badges

Comments

Ray · Accepted Answer · 2009-08-04 19:34:37Z

-1

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

answered Aug 4, 2009 at 19:34

Ray

1,5759 silver badges10 bronze badges

1 Comment

Jon Over a year ago

If it is not related please do not post it.

Collectives™ on Stack Overflow

Mysql "order of operations" Question

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related