MATCH AGAINST with mixed MAX()

[Dan Tappin]

I have a table 'companies' and a table 'feedback'.  Feedback has rows  
with a id_company key that matched the companies.id.  Think of it as  
the ebay rating system.

I have a simple query that works fine:
SELECT companies.id, MIN(feedback.rating), MAX(feedback.rating),  
AVERAGE(feedback.rating)
FROM companies
LEFT JOIN feedback ON companies.id = feedback.id_company
GROUP BY companies.id

I get my results... all is good.

Now I add a MATCH into the loop.  I want to do a full text search on  
an existing index:

SELECT
MATCH (keywords, company) AGAINST ('foo bar') as rank,
companies.id, MIN(feedback.rating), MAX(feedback.rating), AVERAGE 
(feedback.rating)
FROM companies
LEFT JOIN feedback ON companies.id = feedback.id_company
GROUP BY companies.id

and again I get my data... something like:

rank    id    min  max  ave
3.444    1    0    5    5
2.234    2    0    0    0
2.345    3    0    1    1
etc

Now I want to normalize the rank field i.e. rank = rank / MAX(rank):

SELECT
MATCH (keywords, company) AGAINST ('foo bar') / MAX(MATCH (keywords,  
company) AGAINST ('foo bar')) as rank,
companies.id, MIN(feedback.rating), MAX(feedback.rating), AVERAGE 
(feedback.rating)
FROM companies
LEFT JOIN feedback ON companies.id = feedback.id_company
GROUP BY companies.id

Except now I still get the MATCH values not the MAX = 3.444.  I've  
narrowed to down to my JOIN / GROUP BY.  If I get rid of the join the  
MAX works or if I GROUP BY feedback.rating the MAX works but I get  
duplicate companies.id results (i.e. with multiple feedback rows).

Can I do this with a single query??

Thanks,

Dan T


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]