sql - Can I get the minimum of 2 columns which is greater than a given value using only one scan of a table -

this example data (there no indexes , not want create any):

create table tbltest ( int , b int ); insert tbltest ( a, b ) values ( 1 , 2 ), ( 5 , 1 ), ( 1 , 4 ), ( 3 , 2 ) 

i want minimum value in of both column , column b greater given value. e.g. if given value 3 want 4 returned.

this current solution:

select min (submin)  (  select min (a) submin tbltest  > 3 -- returns 5  union   select min (b) submin tbltest  b > 3 -- returns 4 )  

this searches table twice - once min(a) once min(b).

i believe should faster 1 pass. possible?

you want use conditional aggregatino this:

select min(case when > 3 end) mina,        min(case when b > 3 b end) minb tbltest; 

to minimum of both values, can use sqlite extension, handles multiple values min():

select min(min(case when > 3 end),            min(case when b > 3 b end)           ) tbltest 

the issue min return null if either argument null. can fix doing:

select coalesce(min(min(case when > 3 end),                     min(case when b > 3 b end)                    ),                 min(case when > 3 end),                 min(case when b > 3 b end)                ) tbltest 

this version return minimum value, subject conditions. if 1 of conditions has no rows, still return minimum of other value.