[thelist] Weighted Grouped Select -- A stored procedure?

[J. Blanchard]

----
SELECT
    obj.name AS obj_name,
    obj.id AS obj_id,
    Sum(data_type.search_weight) AS relevance,
  FROM data, obj, data_type
  WHERE obj.id = data.obj_id
    AND (Lower(data.value) LIKE '%keyword1%' OR
         Lower(data.value) LIKE '%keyword2%')
    AND data_type.id = data.data_type_id
    AND data_type.search_weight NOT IN (0, NULL)
  GROUP BY obj.id
  ORDER BY relevance DESC, obj_name

This query works great for searching for keywords!  However, if both keywords appear in one data cell, then the relevance isn't increased accordingly.  For example, let's say the search weight for the object's name was 2 and its color was one and have a single object called "blue ball" and had a color of "blue".  If I search for two keywords, "blue" and "ball", this algorithm would give a relevance of 3 (2 for the name, 1 for the color), instead of 5 (4 points for both words in title + 1 more for color).
----

Why not use a stored procedure? That way you can insert conditional expressions increasing the value of undervalued objects and then return the sum accordingly. I have done something similiar before but haven't tested doing something like this with your SQL code.

TTFN,

Jay Blanchard
Web Engineer & Understanding Person
Thermon Industries
1.800.820.HEAT x327

___________________________________________________________________________
Visit http://www.visto.com/info, your free web-based communications center.
Visto.com. Life on the Dot.