MySql sort by highest value with inner join and subquery

Question

I have a query that has a sub query that returns a count of records from another table, I'm having trouble ordernar the largest number of this counter

SELECT respostas.id,
       respostas.cmm,
       respostas.topico,
       respostas.usuario,
       respostas.resposta,
       perfis.nome,
       perfis.sobrenome,
       respostas.datahora,
       (
           SELECT count(id) 
           FROM likes 
           WHERE respostas.id = resposta
       ) AS total
FROM respostas
INNER JOIN perfis ON respostas.usuario = perfis.id
INNER JOIN likes ON respostas.topico = likes.topico
WHERE respostas.cmm = 28
        AND respostas.topico = 38
ORDER BY respostas.id ASC, total ASC
                LIMIT 0,20`enter code here`

I want to sort by the total column and can not. Sorting by total does not work, only ordered by id

score 0 · Answer 1 · edited May 23 '17 at 12:21

0

You can choose which column to order by numerically:

SELECT           
       (
           SELECT count(id) 
           FROM likes 
           WHERE respostas.id = resposta
       ) AS total,
       respostas.id,
       respostas.cmm,
       respostas.topico,
       respostas.usuario,
       respostas.resposta,
       perfis.nome,
       perfis.sobrenome,
       respostas.datahora
FROM respostas
INNER JOIN perfis ON respostas.usuario = perfis.id
INNER JOIN likes ON respostas.topico = likes.topico
WHERE respostas.cmm = 28
        AND respostas.topico = 38
ORDER BY 1, respostas.id
                LIMIT 0,20

What is the purpose of Order By 1 in SQL select statement?

edited May 23 '17 at 12:21

Community

1
1

answered Jul 21 '14 at 20:02

Andreas

4,937
2
25
35

1

I think you'll want to remove `total ASC` from the `ORDER BY` clause. – Marcus Adams Jul 21 '14 at 20:25
Thanks Marcus. Updated. – Andreas Jul 21 '14 at 20:47

MySql sort by highest value with inner join and subquery

1 Answers1