SQL: user variable increment for each rows

Question

EDIT : my question is not clear, so I've reformulated it here : Order sql result by occurrence of a set of keywords in a string

I'm improving my search system for my website. I'm trying to use and increment variables in sql request, like that...

SET @titlematch = 0;
SELECT *,
CASE 
    when title like '%apple%' then (SET @titlematch = @titlematch+1)
    when title like '%orange%' then (SET @titlematch = @titlematch+1)
    when title like '%other_keyword_searched%' then (SET @titlematch = @titlematch+1)
    (...)
END,
(...)
FROM pages  
(...)
ORDER by @titlematch desc

In fact, titlematch should be incremented each time that a keyword is in the title. If there's "apple" and "orange" in the title, titlematch should be equal to 2. But actually, it doesn't work...

(sorry for my english)

have you tried removing `SET` in case statement like this `CASE WHEN..THEN (@titlematch = @titlematch+1)` , also can you provide the error you are getting. — Ashish Gaur, Sep 02 '13 at 09:59
why can't you use `COUNT` with condition `LIKE '%apple% OR '%orange%' OR '%other_keyword_searched%'` — Ashish Gaur, Sep 02 '13 at 10:12
@snyder you think it could increment titlematch each time that a condition is true, with count() ? how do you do that ? — Maxime R., Sep 02 '13 at 10:18
You can select those rows in a `subquery` using `LIKE '%apple% OR '%orange%' OR '%other_keyword_searched%'` and then you can use `COUNT(*)` in `outer query` to get the no. of rows. — Ashish Gaur, Sep 02 '13 at 10:21
@snyder but I don't want the number of rows with a title 'like' the search keywords, I wan't to calculate the relevance of the title of each rows — Maxime R., Sep 02 '13 at 10:24

score 0 · Answer 1 · answered Sep 02 '13 at 10:18

0

I think it fails because it must handle all the data,if title like someWordYouDontAcccountFor it will fail.You must account for all possible cases or use else.

answered Sep 02 '13 at 10:18

Mihai

26,325
7
66
81

score 0 · Answer 2 · answered Sep 02 '13 at 10:34

In response to your comment (Yes, always), I rewrite your query in this way:

SELECT *, (select count(*) from pages p2 where p1.field_date < p2.field_date) as pos
(...)
FROM pages p1
(...)
ORDER by (select count(*) from pages p2 where p1.field_date < p2.field_date) desc

In this way you count every rows before the actual (I've based my count on ipotetic field_date but if you want you can change your condition), so you have an incremental value for each row, and finally, I add this condition in order by clause.

Tell me if it's OK

SQL: user variable increment for each rows

2 Answers2