MySQL IF THEN in WHERE clause

Question

I have table with a enum column with possible values of 'relative' and 'absolute'. There maybe duplicate rows with the difference being that column.

So normally I would select a row with the 'absolute' value but I need and if caluse to check if there is a 'duplicate' row where the column is 'relative' and then select that row instead (if there is a reletive row, there will always be absolute row too)

pseudo code:

select *
  from table
  where non_unique_col = 123
    and IF (has result row with 'relative'
        then return that row
        else return 'absolute' row)

Answer 1

You could try this:

SELECT *
FROM `table`
WHERE `non_unique_col` = 123
ORDER BY `absolute_relative_col` DESC
LIMIT 1

This way, if there is exactly one result, no problem the, if there are more, you get the 'relative' one.

EDIT:

As per @Travesty3 suggestion, I'd like to underline that this query is made on the assumption that (non_unique_col + absolute_relative_col) makes a unique_col, which is based on OP statements that

There maybe duplicate rows with the difference being that column

and

if there is a relative row, there will always be absolute row too

EDIT 2:

A more generic solution could be the following:

SELECT *
FROM `table` as t1
JOIN (
  SELECT non_unique_col, absolute_relative_col
  FROM `table`
  WHERE `absolute_relative_col` = 'relative'
) as t2 USING (non_unique_col)
WHERE t2.absolute_relative_col = 'relative' OR (
  t2.absolute_relative_col IS NULL
  AND t1.absolute_relative_col = 'absolute'
)