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?

Answers


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;

Need Your Help

Connection Time-out when using smtp mail message

c# .net email

So yes, I have looked at every other post and none of them seem too fix my problem, the SMTP server is Google so its never offline, I have tried combinations of port 25 & 465 with SSL enabled but

C# FlowLayoutPanel scrollBar

c# .net user-controls scrollbar flowlayoutpanel

I'm making an instant messaging application in C#. The problem that I'm facing right now is that for the contacts list I've made a custom control extendinguserControl, which contains aFlowLayoutPa...