Re: Search (select) options

Jeff Davis wrote:
>
> I would like to be able to use searches that seem somewhat intelligent.
> Can you 'ORDER BY' number of matching 'OR' clauses? For example, someone
> searches for "x y z", so I would do "select * from mytable where col1
> like '%x%' or col1 like '%y%' or col1 like '%z%';", but I want it to
> order by number of matches (so a match of y and z would turn up before a
> match of just x).
>
> If anyone has suggestions, or can point me to some reading, I would
> really appreciate it. The only thing I can think of is a complicated
> application-side program.


chrisb=# create table t(a text, b text, c text);
CREATE
chrisb=# insert into t values(null, 'x', null);
INSERT 18955 1
chrisb=# insert into t values(null, 'x', 'x');
INSERT 18956 1
chrisb=# insert into t values(null, 'x', null);
INSERT 18957 1
chrisb=# insert into t values(null, 'x', 'x');
INSERT 18958 1
chrisb=# insert into t values('x', 'x', 'x');
INSERT 18959 1
chrisb=# insert into t values(null, null, null);
INSERT 18960 1
chrisb=# select * from t;
 a | b | c
---+---+---
   | x |
   | x | x
   | x |
   | x | x
 x | x | x
   |   |
(6 rows)

chrisb=# select *, case when a='x' then 1 else 0 end + case when b='x'
then 1 else 0 end + case when c='x' then 1 else 0 end as match from t
order by match;
 a | b | c | match
---+---+---+-------
   |   |   |     0
   | x |   |     1
   | x |   |     1
   | x | x |     2
   | x | x |     2
 x | x | x |     3
(6 rows)

chrisb=# select *, case when a='x' then 1 else 0 end + case when b='x'
then 1 else 0 end + case when c='x' then 1 else 0 end as match from t
order by match desc;
 a | b | c | match
---+---+---+-------
 x | x | x |     3
   | x | x |     2
   | x | x |     2
   | x |   |     1
   | x |   |     1
   |   |   |     0
(6 rows)