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)