CODEWITHSUNDEEP
mysqlsqlgroup-bymatchmatch-against
user3258827
user3258827

Reputation: 1

group by order by match against

I want use ORDER BY and GROUP BY on the same query. But If I use Group BY the relevance don't work correctly.

This is the code:

(SELECT *, MATCH(keywords) AGAINST('$busqueda') AS relevancia 
FROM libros 
WHERE MATCH(keywords) AGAINST('$busqueda') AND tipo = 'nuevo'
GROUP BY id_libro 
ORDER BY relevancia DESC, posicion ASC, precio DESC LIMIT $numero_limite)

If I get make the equivalente of this (another example) but with the previous code my query will works correctly, because on this example the order by and the group by works good, but not have the match against that I need:

SELECT
   t.id, t.catid, t.name, t.userid, t.subject  
FROM
    (SELECT id,  catid,  name,  userid,  subject , thread
    FROM mensajes
    ORDER BY t.id DESC) t
GROUP BY t.thread  
LIMIT 6;

This is the table (Sorry, the forum does not let me put pictures to not have 10 points):

http://www.forosdelweb.com/f86/group-match-against-respetando-relevancia-1089769/#post4561689

You can see the captures/Images on that link.

As you can see on the image 3, the result is incorrect. Must be the id_unico 9, because have a posicion 21.

Does anyone know?

Upvotes: 0

Views: 1159

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Your second example is simply wrong. It is using an extension to group by in MySQL that is explicitly documented to return arbitrary column values, not the values that your query requires. You can read about it here.

You can do what you want by finding the maximum relevancia using a subquery, as in:

select l.*
from (SELECT *, MATCH(keywords) AGAINST('$busqueda') AS relevancia 
      FROM libros l
      WHERE MATCH(keywords) AGAINST('$busqueda') AND tipo = 'nuevo'
     ) l
where not exists (select 1
                  from libros l2
                  where l2.id_libros = l.id_libros and
                        l2.tipo = l1.typo and
                        MATCH(l2.keywords) AGAINST('$busqueda') > l.relevancia
                 )

ORDER BY relevancia DESC, posicion ASC, precio DESC
LIMIT $numero_limite;

Upvotes: 1

Related Questions