SMW SMW - 4 months ago 8
SQL Question

find a row based on serverl creiteria

I have a table as follows:

id var status warehouse
1 x True 1
2 x False 1
3 y False 1


I want to write a query where I give it
var
and it returns me only one row as follows.
if it has status=True it will return this row first, if not it will return any status. if none found it will return nothing.

For example
for
x
it will return
id=1
(2 is wrong!)
for
y
it will return
id=3

for
z
it will return nothing.

I know I can do it in a function with 2 queries like:

select id into choose_id
from tab
where var=given_input and and status
limit 1;

IF choose_id IS NULL THEN
select id into choose_id
from tab
where var=given_input
limit 1;
END IF;

continue work with choose_id


where
given_input
is
'x'
or
'z'
or
'y'


is it doable in a single query without
IF
statment?

Answer

You can use order by and limit:

select t.*
from tab t
where var = $var
order by (case when status = true then 1 else 2 end)
limit 1;