seo Question posted in
Some basic information to get you started with this can be found here.

mySQL Switch: How to give "OR" priority or importance in a SELECT statement to get a default row – SEO

Is there a simple way to do a Switch in mySQL? Consider this:

SELECT * FROM table WHERE
    a=1 OR
    b=2 OR
    c=3 OR
    d=4 OR
    e=5 OR
    f=6
LIMIT 1;

This will return a random row which matches any part of the OR clause. However, if a is the most important column, b is the next most important, etc, how do we alter the query to select only a single row in order of importance? In other words, we need to first check if a=1 and return it if true, else check b=2, and so on down the list until we find a hit.

The practical use for a query like this is a settings table where we want to see if the user or their administrator, or their organisation has added a custom settings row. If not, we need to select the default settings for that country.

I am thinking possibly of using the ORDER BY clause to return the most important row or maybe using a long messy if/else to find the most important result but I can’t figure out the syntax.

This is very difficult to search so I’ve added some keywords to the title for SEO.

Tags:

2

Answers


  1. 0

    You can use CASE, though this is getting ugly soon. It would be better to have your data normalized so you could query it better.

    SELECT *
FROM table
WHERE
  CASE WHEN a = 1
    THEN a = 1
  ELSE
    CASE WHEN b = 2
      THEN b = 2
    ELSE
      CASE WHEN c = 3
        THEN c = 3
      END END END
LIMIT 1;
    Login or Signup to reply.
  2. 0

    What you need here is ORDER BY CASE clause.

    Like this:

    SELECT * FROM table WHERE
a=1 OR
b=2 OR
c=3 OR
d=4 OR
e=5 OR
f=6
ORDER BY CASE WHEN a=1 THEN 0
 WHEN b=2 THEN 1
 WHEN c=3 THEN 2
 ...
 ELSE 3
END
LIMIT 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search