Firebird Sql Challenge - Return One Row That Has The Data When Select Returned Two Rows

I have a quite unique need to make select always return one row My SQL: select * from table1 Where (table1.pk = :p1) or (table1.fk1 = :p1) The above SQL always has two cases for r

Solution 1:

You can use the first clause of the select statement to get only 1 row.

Given your specific conditions, you can order the result set descending by the rest of the fields to be sure the null row is selected only in case there's no data row (null goes first in firebird 2.5, but AFAIK this changed somewhere in the last versions, so check your specific version before applying this).

Your final query will look like this:

selectfirst1*from table1 
 where (table1.pk = :p1) 
    or (table1.fk1 = :p1)
 orderby somecolumn;

somecolumn being the most relevant of the other fields that can contain null values.

you can test this with this statements:

--two rows, one with ID and values and the other with ID and nullwith q1 as (
      select1 id, 'data' othercolumn
        from rdb$database
      unionselect2 id, null othercolumn
        from rdb$database
     ) 
selectfirst1*from q1
 orderby othercolumn nulls last;

--versus:--onw row with ID and nullwith q1 as (
      select2 id, null othercolumn
        from rdb$database
     ) 
selectfirst1*from q1
 orderby othercolumn nulls last;