Result ranking from a multi-part query

I am trying to create a single query that will allow me to obtain a set of results ranked in best match order from a relational database queried in two ways (ie on place and keyword) against a single search list that may contain keywords, places or both.

The following query results in one row being returned for each match, but requires further processing in my application to create the ranking:

SELECT photo.ID
FROM photo, phkw, keyword
WHERE keyword IN (listOfSearchTerms) AND photo.ID=phkw.phID AND phkw.kwID=keyword.ID UNION ALL
SELECT photo.ID
FROM photo, photoPlace, places
WHERE placename IN (listOfSearchTerms) AND photo.placeID=photoPlace.phID AND photoPlace.placeID=places.ID ORDER BY photo.ID

Is there some way in which I could return photo.ID and a "score" indicating the number of matches?

Can GROUP BY be used across the entire statement?

Is there some way the second part of the query could be ORed with the first whilst generating only one row for each placename match?

Many thanks in advance for any assistance with this. Received on Sun Jul 22 2001 - 23:29:04 CEST