Re: Search (select) options

Perhaps you can use the UNION statement like so...

SELECT * FROM mytable WHERE col1='x'
UNION
SELECT * FROM mytable WHERE col1='y'
UNION
SELECT * FROM mytable WHERE col1='z'

Although, I have never tried stacking them before so I don't know if the
above will work (with more than one UNION).

Of course, this means there are 3 separate select statements instead of
your 1 but perhaps it isn't as inefficient as it sounds since pg should
process the whole transaction in the one commit.

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.
>
> Thanks,
> Jeff Davis
>
>
>