-2

how can I display 1 to 10 score on my query.

for e.g. I get 10 records with this and I want to match highest with 10 and so on.

SELECT DISTINCT c.id, c.title, cv.title2 , match ( c.title, c.title2, c.title3, c.title4 ) against ('Support' IN BOOLEAN MODE) AS score
FROM table1 AS c 
WHERE match ( c.title, c.title2, c.title3, c.title4 ) against ('Support' IN BOOLEAN MODE)

When I do a search my database returns set of results.

If keyword matches 50 keywords in c.title, c.title2, c.title3, c.title4 ( this is just an example) then I want to display 10 out of 10 as the score and say it's a perfect match.

If keyword matches 45 keywords in c.title, c.title2, c.title3, c.title4 ( this is just an example) then I want to display 9 out of 10 as the score.

If keyword matches 30 keywords in c.title, c.title2, c.title3, c.title4 ( this is just an example) then I want to display 7 out of 10 as the score, some perfect.

If keyword matches 2 keywords in c.title, c.title2, c.title3, c.title4 ( this is just an example) then I want to display 1 out of 10 as the score some keywords match.

and so on.

brenjt
  • 15,997
  • 13
  • 77
  • 118
Lalajee
  • 139
  • 1
  • 9
  • cant you just add 1 to 10 in the output loop? –  May 30 '12 at 21:39
  • I need to display score on keyword matchs – Lalajee May 30 '12 at 21:42
  • 3
    You really need to clarify what you want to do, because this must be one of the vaguest questions I have ever seen. E.g. give an example data set with the desired output. What should this score mean? Just the order of appearance? Do you always get exactly 10 records? Explain. – Bart May 30 '12 at 22:45

2 Answers2

2

I think you're just missing the obvious solution. Just sort by the score...

SELECT DISTINCT c.id, c.title, cv.title2 , match ( c.title, c.title2, c.title3, c.title4 ) against ('Support' IN BOOLEAN MODE) AS score
FROM table1 AS c 
WHERE match ( c.title, c.title2, c.title3, c.title4 ) against ('Support' IN BOOLEAN MODE)
ORDER BY score DESC
MetalFrog
  • 9,943
  • 1
  • 22
  • 24
  • I have 4 filed on website one called score and the others are c.id, c.title, cv.title2. but the score is always 1 or 0 – Lalajee May 31 '12 at 19:48
  • something like http://hiring.monster.co.uk/jcm/resumesearch/EnhancedSearchResults.aspx?seng=trovix&co=UK&jt=Desktop%2520Administrator&mdatemaxage=1576800&rb=1#s=grid – Lalajee May 31 '12 at 19:53
  • In this query ``score`` is the fulltext score. – MetalFrog Jun 01 '12 at 01:43
2

Ok I think I got it.

SELECT DISTINCT c.id, c.title, cv.title2 , (match ( c.title, c.title2, c.title3, c.title4 ) AGAINST('desktop') * 4.5 )  AS score
FROM job
WHERE
match ( c.title, c.title2, c.title3, c.title4 ) AGAINST('desktop')
ORDER BY score DESC

Please let me know if I can write it better.

Lalajee
  • 139
  • 1
  • 9