How to use OR in query with priority?

user_13053049 0
Viewed 69

In a simple query like

SELECT * 
FROM table
WHERE id='x' OR id='y'

How to give a priority to get only one of the WHERE clauses?

I mean getting rows with id='x'. Only if there is no row, get row with id='y'.

In other words, do not use id='y' if there is a row for id='x'.

mysql sql
3 Answers

If you want only one row, the simplest method is limit:

SELECT t.* 
FROM table t
WHERE id IN ('x', 'y')
ORDER BY (id = 'x') DESC
LIMIT 1;

The id = 'x' in the order by uses the fact that MySQL treats boolean values as integers in a numeric constant. So "true" is treated as 1 and "false" as 0. The DESC puts the true values (i.e. 'x') first.

If there are multiple possible rows that could be returned, then NOT EXISTS is a possibility:

select t.*
from t
where t.id = 'x' or
      (t.id = 'y' and
       not exists (select 1 from t t2 where t2.id = 'x')
      );

In MySQL, this version might be optimized better using union all, if you have an index on id:

select t.*
from t
where t.id = 'x' 
union all
select t.*
from t
where t.id = 'y' and
      not exists (select 1 from t t2 where t2.id = 'x');
user_11440350

You can use case statement or limit clause. Limit clause is the better option. Do not forget to use order by while using limit clause

select * from
(SELECT * 
FROM table
WHERE id='x' OR id='y')
where
case when id = 'x' then id = 'x'
     when id = 'Y' then id = 'Y' end ;


SELECT * FROM table WHERE id='x' OR id='y' order by id limit 1;
user_44736150

Here is another way to fetch same data with help the Rank basis, And it's support multiple values as well as, based on your select statement.

SELECT TOP 1 *, (RANK() OVER (ORDER BY VAL DESC )) AS RANK_N FROM TEST_TABLE T WHERE T.ID IN('X','Y') ORDER BY RANK_N

user_23583230
Related