How to dynamically loop columns when `select where like` till result has only one row?

create table test(col1 int, col2 varchar,col3 date);

insert into test values (1,'abc','2015-09-10');

insert into test values (1,'abd2','2015-09-11');

insert into test values (21,'xyz','2015-09-12');

insert into test values (2,'xyz','2015-09-13');

insert into test values (3,'tcs','2015-01-15');

insert into test values (3,'tcs','2016-01-18');

What I want:

if
select col1,col2 from test where col1 LIKE '%3%';
has more than 1 rows,then

if
select col1,col2 from test where col1 LIKE '%3%' AND col2 LIKE '%t%';
has more than 1 rows,then

select col1,col2 from test where col1 LIKE '%3%' AND col2 LIKE '%t%' AND col3 LIKE '%2016%';

Another case:

if
select col1,col2 from test where col1 LIKE '%1%';
has more than 1 rows,then

if
select col1,col2 from test where col1 LIKE '%1%' AND col2 LIKE '%a%';
has more than 1 rows,then

select col1,col2 from test where col1 LIKE '%1%' AND col2 LIKE '%a%' AND col3 LIKE '%2015%';

if all columns looped,output regardless the number of rows

How to do it in postgresql?

Answer

How to dynamically loop columns when select where like till result has only one row?

You can use order by and limit:

select col1, col2
from test
where col1 like '%3%' 
order by (col1 LIKE '%3%')::int + (col2 LIKE '%t%')::int + (col3 LIKE '%2016%')::int desc
limit 1

Advertisement

Answer