database - MySql, Select, left join, group by, limit.. and where i've to place ORDER? -
i've confusionary quesry string @ moment appears this:
select sql_calc_found_rows m.*, ( select count(1) votazioni_messaggi v v.idmeggaggio=m.id ) votato, avg(voto) votazionemedia, n.nomeutente, messages m left join votazioni_messaggi v on v.idmessaggio = m.id left join utenti n on a.idutente=n.id n.iduser='$iduser' , m.genre in('animal', 'love') group m.id, limit $partenza, 20
in other words, i've select 20 messages @ time haves genre "animals" , "love", each of retrieve info user , calculate vote average. counting , grouping can know if user had voted or not message.
now i've set last clause order messages insertion time (i've data in messages.insertiontime table).
the clause should order messages.insertiontime how can know in exact point i've place it?
try this:
select sql_calc_found_rows m.*, (select count(1) votazioni_messaggi v v.idmeggaggio=m.id ) votato, avg(voto) votazionemedia, n.nomeutente, messages m left join votazioni_messaggi v on v.idmessaggio = m.id left join utenti n on a.idutente=n.id n.iduser='$iduser' , m.genre in ('animal', 'love') group m.id order m.insertiontime limit $partenza, 20;
you had @ least 2 problems. 1 comma @ end of group by
. other using table name instead of table alias.
Comments
Post a Comment