Search code examples
sqlhql

Select using WHERE if any exist, else don't use WHERE

I want to select from a table where column = 'n'. If the result has 0 rows, I want to remove that conditional.

Some pseudo-sql might be:

SELECT * FROM MY_TABLE
WHERE COL_A = 1 AND COL_B = 'mystring'
ELSE
    SELECT * FROM MY_TABLE
    WHERE COL_A = 1;

I will only be using the first row in either result, so it would also be acceptable to somehow sort the result such that rows where COL_B = 'mystring' appear at the top.

Solution

  • If you do not have too many values for each col_a value, you can use:

    select t.*
from t
where col_a = 1
order by (case when col_b = 'mystring' then 1 else 2 end);

    Just use the first row in the result set.