views:

306

answers:

3

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

+3  A: 
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).

Quassnoi
+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.
R. Bemrose
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?
Michael
@Michael: if you want highest `id`'s first, use `ORDER BY criteria = $criteria DESC, id DESC`
Quassnoi
Thanks, then what does the DESC in criteria = $criteria DESC do as opposed to criteria = $criteria
Michael
@Michael: it will first select rows with `$criteria` (from highest to lowest `id`), then all other rows (again, from highest to lowest `id`)
Quassnoi
@Michael: `criteria = $criteria DESC` is a boolean expression. `TRUE` are `1` and `FALSE` are `0`. `DESC` makes `TRUE` values come first.
Quassnoi
Oh wow cool. Thanks!
Michael
A: 

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

Ray
A: 
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.

Jonathan Leffler