Re: Re: [SQL] ORDER BY what?

On Wednesday 13 June 2001 04:26, Martín Marqués wrote:
> On Mié 13 Jun 2001 16:16, Bruno Wolff III wrote:
> > On Tue, Jun 12, 2001 at 03:53:22PM +0300,
> > > select * from tab1 where col1 like '%word%' or col2 like '%word%' and
> > > col3 like '%word%'

> > > and I want to order by the amopunt of matches that a matching register
> > > has. Something like, if it matches all the ORs, then it should go
> > > first, and if it matches only one of the ORs it should go last.
> > > Or maybe even have several words trying to match one of the columns.

> > You could do this by computing a value based on the number of parts
> > that matched and order by it.

> Could you give me a hint on this? Do I have to use PLSQL? Triggers?
> Something else?

SELECT *,
((CASE WHEN col1 like '%word%'
THEN 1
ELSE 0
END)
+
(CASE WHEN col2 like '%word%'
THEN 1
ELSE 0
END)
+
(CASE WHEN col3 like '%word%'
THEN 1
ELSE 0
END))
AS matches
FROM tab1
WHERE
col1 like '%word%' or
col2 like '%word%' and
col3 like '%word%'
ORDER BY matches desc;

:-)

Shouldn't be terribly hard to generate this programmatically, but it _is_ a
bear to type by hand. If all conditions were guaranteed to be OR (you have
an AND up there) you could replace the where clause in my example with:

WHERE matches > 0

This counting could slow your queries down significantly, though. You'd have
to try performance testing of it.

If you wanted the top fifty of these, you could use LIMIT appropriately.

Been there, done that.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11