PHWinfo - Afficher un message - Big SELECT: ordering results by where matches are found

10/09/2007, 16h21

At 11:01 -0400 10/9/07, Baron Schwartz wrote:
>I've built similar systems with a series of UNION queries. Each UNION has a column for "relevance", which can be a sum of CASE statements, such as
>
>IF(<name matches>, 1, 0) + IF(<text matches>, 1, 0) AS relevance...
>
>The entire UNION can then be ordered by relevance. You could also just add in an arbitrary number in each UNION, to get the effect of ordering by where in the hierarchy the match is found.

Oo-er. This sounds marvellous, and I /think/ I see what you're getting at, but it's a bit beyond anything I've done before - never used UNION for instance. Can you perhaps go into a little more detail?

--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/

The nice thing about standards is that there are so
many of them to choose from.
-- Andrew S. Tanenbaum

10/09/2007, 16h21	#3
Chris Sansom Messages: n/a Hébergeur:	Re: Big SELECT: ordering results by where matches are found At 11:01 -0400 10/9/07, Baron Schwartz wrote: >I've built similar systems with a series of UNION queries. Each UNION has a column for "relevance", which can be a sum of CASE statements, such as > >IF(<name matches>, 1, 0) + IF(<text matches>, 1, 0) AS relevance... > >The entire UNION can then be ordered by relevance. You could also just add in an arbitrary number in each UNION, to get the effect of ordering by where in the hierarchy the match is found. Oo-er. This sounds marvellous, and I /think/ I see what you're getting at, but it's a bit beyond anything I've done before - never used UNION for instance. Can you perhaps go into a little more detail? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ The nice thing about standards is that there are so many of them to choose from. -- Andrew S. Tanenbaum