Page 1 of 1

Challenging fulltext query

Posted: Fri Jul 01, 2005 2:37 pm
by voltrader
What I'm trying to do is sort by rating but keep relevancy of what's searched for.

I'm finding it hard to articulate exactly what's happening. Please bear with me.

Say I have the following records in the "city" field:

city
San Francisco, CA
San Francisco
San Jose
San Dimas

Their corresponding ratings are:

ratings
3
2
5
4

User searches for "San Francisco" and wants to sort by rating. I use the following fulltext query to pull up the results:

Code: Select all

SELECT *, (MATCH(details) AGAINST ('$search_term')) AS score
WHERE (MATCH(details) AGAINST ('$search_term')) ORDER BY ratings DESC, score DESC
This returns:
San Jose 5
San Dimas 4
San Francisco, CA 3
San Francisco 2

When I would like:
San Francisco, CA 3
San Francisco 2

I want to somehow use the alias variable 'score' to order the results as well as take ORDER BY ratings into account.

How can I accomplish this?

Posted: Mon Jul 04, 2005 10:06 am
by kendall
Yo,

didnt i answer this question?

їcode]ODERBY Score Desc, rating Ascї/code]

since score is first it will take precedence in sorting then rating

Posted: Mon Jul 04, 2005 1:21 pm
by voltrader
In practice the sort is among tens of thousands of records, so almost every score will be unique.

As a result a secondary sort by rating will not do anything.

Perhaps I can "tune-out" score by making them 1 decimal place.