Consulta para un ranking

[sdavila21]

Hola buenas,
llevo 3 días intentando sacar una consulta y no doy con la tecla, me explico :

Tengo dos tablas : usuarios y mensajes

usuarios tiene estos campos : iduser,usuario,password,nombre,apellidos
mensajes tienes : idmensaje,fechahora,emisor(id numero de usuario),receptor(id numero de usuario),texto

Lo que yo necesito es sacar los usuarios con mas mensajes y tener una columna con la posición del ranking. Es decir yo tengo Usuario A (2 mensajes),Usuario B(1) y Usuario C (3).

Necesito hacer una consulta que me muestre algo como esto:

1 - Usuario C - 3 Mensajes
2 - Usuario A - 2 Mensajes
3 - Usuario B - 1 Mensaje

Tengo esta consulta :
[

Código MySQL:

select  @curRank := @curRank + 1 AS rank,u.usuario,count(emisor) as cantidad from (SELECT @curRank := 0) r,mensaje
inner join usuario u on mensaje.emisor=u.iduser 
group by emisor ORDER BY cantidad DESC;
 

Pero lo que me duelve es:

3 - Usuario C - 3
1 - Usuario A - 2
2 - Usuario B - 1

La primera columna realmente es el id del usuario de la tabla usuario pero no es el del ranking.

Espero haberme explicado y que alguien pueda ayudarme.

Un saludo y gracias

[gnzsoloyo]

Lo que te devuelve es correcto, dado que estas ordenando en forma descendente, por las cantidades.
¿Se entiende?

[sdavila21]

Hola,
Pero lo que necesito que me devuelva es :

1 - Usuario C - 3
2 - Usuario A - 2
3 - Usuario B - 1

¿Como puedo hacerlo?

Gracias por la ayuda

[gnzsoloyo]

Pues es medio obvio, lo ordenas por la columna 1...

Código MySQL:

...
ORDER BY 1
 

[sdavila21]

Creo que no nos estamos entendiendo. Necesito que me ordene por los que tienen mas mensajes y una vez tengo eso el que mas tiene tiene que ser el 1 en el ranking no el 3 como me muestra actualmente.

Ahora :

3 - Usuario C - 3
1 - Usuario A - 2
2 - Usuario B - 1

Lo que necesito :

1 - Usuario C - 3
2 - Usuario A - 2
3 - Usuario B - 1

Espero haberme explicado porque muy bien no se me da.

Gracias

[leonardo_josue]

Hola sdavila21:

Para entender en donde está el problema, debes recordar que la cláusula ORDER BY se ejecuta AL FINAL DE LA CONSULTA, es decir, primero se realizaría el SELECT con sus respectivos cálculos y al final la ordenación, es por eso que no coinciden los datos que quieres... Dado que el ranking depende en primer lugar del ORDEN, entonces DEBES FORZAR A QUE PRIMERO SE ORDENEN y después realizar la numeración para obtener el ranking, esto lo puedes hacer de distintas formas, la más simple es simplemente metiéndolo en una subconsulta. Checa este ejemplo:

Supongamos que tienes una tabla más o menos así:

Código MySQL:

mysql> SELECT * FROM mensajes;
+------+-----------+
| id   | usuario   |
+------+-----------+
|    1 | usuario A |
|    2 | usuario A |
|    3 | usuario B |
|    4 | usuario B |
|    5 | usuario B |
|    6 | usuario B |
|    7 | usuario C |
|    8 | usuario C |
|    9 | usuario C |
+------+-----------+
9 rows in set (0.01 sec)
 

Para obtener el total de mensajes, simplemente haces un COUNT y ordenas por el campo calculado:

Código MySQL:

mysql> SELECT usuario, COUNT(usuario) total
    -> FROM mensajes
    -> GROUP BY usuario 
    -> ORDER BY total DESC;
+-----------+-------+
| usuario   | total |
+-----------+-------+
| usuario B |     4 |
| usuario C |     3 |
| usuario A |     2 |
+-----------+-------+
3 rows in set (0.01 sec)
 

Ahora bien, este resultado es el que quieres numerar o "rankear", por lo tanto, lo pones como una SUBCONSULTA, para numerar los resultados ya ordenados. En otras palabras, haces algo como esto:

Código MySQL:

mysql> SET @rownum := 0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT @rownum:=@rownum+1 rank, usuario, total
    -> FROM
    -> (  SELECT usuario, COUNT(usuario) total
    ->    FROM mensajes
    ->    GROUP BY usuario ORDER BY total DESC) T;
+------+-----------+-------+
| rank | usuario   | total |
+------+-----------+-------+
|    1 | usuario B |     4 |
|    2 | usuario C |     3 |
|    3 | usuario A |     2 |
+------+-----------+-------+
3 rows in set (0.00 sec)
 

Se entiende donde estaba el problema??? haz la prueba y nos comentas.

Saludos
Leo.

[sdavila21]

Ostras la verdad que es perfecto. Ahora si me funciona.
La otra duda es yo una vez tengo esto, como puedo indicar que posicion tiene el usuario con id 1 por ejemplo.
En este caso lo tengo en la tabla usuarios y los mensajes en otra donde emisor y receptor son fk de usuarios.

Gracias por la ayuda.

[gnzsoloyo]

Necesitas obtener el ID del usuario en cuestión junto con el ranking y la suma total de cada uno.
La posición del ranking te permitirá acceder al ID en la tabla resultado.
El ranking no es útil para identificar al usuario si no es en un momento dado del tiempo. Por eso necesitas al mismo tiempo su ID.