Re: Query help

Madison Kelly wrote:
> Hi all,
>
>   I've got a query that looks through a table I use for my little search
> engine. It's something of a reverse-index but not quite, where a proper
> reverse index would have 'word | doc1, doc3, doc4, doc7' showing all the
> docs the keyword is in, mine has an entry for eac
>
>   I've got a query like:
>
> SELECT
>     sch_id, sch_for_table, sch_ref_id, sch_instances
> FROM
>     search_index
> WHERE
>     (sch_keyword LIKE '%digi%' OR sch_keyword LIKE '%madi%')
> AND
>     sch_for_table!='client'
> AND
>     ... (more restrictions)
> ORDER BY
>     sch_instances DESC;
>
>   This returns references to a data column (sch_ref_id) in a given table
> (sch_for_table) for each matched keyword.
>
>   The problem I am having is that two keywords might reference the same
> table/column which would, in turn, give me two+ search results pointing
> to the same entry.
>
>   What I would like to do is, when two or more results match the same
> 'sch_ref_id' and 'sch_for_table' to merge the results. Specifically, the
> 'sch_instances' column is the number of times the given keyword is found
> in the table/column. I'd like to add up the number in the duplicate
> results (to give it a higher accuracy and move it up the search results).

You'll want something like:

SELECT
     sch_id, sch_for_table, sch_ref_id,
     SUM(sch_instances) AS tot_instances
...
GROUP BY
     sch_id, sch_for_table, sch_ref_id
ORDER BY
     tot_instances DESC;

The key word to search the manuals on is "aggregates" (sum(), count() etc).

--
   Richard Huxton
   Archonet Ltd