MYSQL - problem with ORDER BY of a column of equal values

Question

MYSQL - problem with ORDER BY of a column of equal values

Navigation

#1 by (4 votes)

0

I have the following MYSQL query:

SELECT *, 
       (
          SELECT SUM(points) 
          FROM point 
          WHERE employeeId=employee.id
        ) as totalPoints 
FROM employee 
ORDER BY totalPoints DESC

As you can see is a simple list by the number of points of each worker, the goal is to form a simple ranking by score, the problem is that there are many workers with the same score (example 10) then when making the query the position starts to vary. For example, I get this list:

nombre    | apellido | puntaje
--------------------------------
Juan      | Perez    | 120
Pepe      | Suarez   | 100
Ricardo   | Cruzado  | 80
Carlos    | Medina   | 80
Jesus     | Segura   | 80
Alberto   | Veocinco | 50
Guillermo | Cabrejos | 20
Antonio   | Pereyda  | 10

Now focus on the 3 workers who have score 80, every time I call the query Mr. Ricardo crossed for example that is before Mr. Carlos Medina can appear below it or at the end and the positions of the same vary every time I call the consultation, for example I get the following:

nombre    | apellido  | puntaje
--------------------------------
Juan      | Perez     | 120
Pepe      | Suarez    | 100
Jesus     | Segura    | 80
Carlos    | Medina    | 80
Ricardo   | Cruzado   | 80
Alberto   | Veocinco  | 50
Guillermo | Cabrejos  | 20
Antonio   | Pereyda   | 10

and if I call again I get:

nombre    | apellido  | puntaje
--------------------------------
Juan      | Perez     | 120
Pepe      | Suarez    | 100
Jesus     | Segura    | 80
Ricardo   | Cruzado   | 80
Carlos    | Medina    | 80
Alberto   | Veocinco  | 50
Guillermo | Cabrejos  | 20
Antonio   | Pereyda   | 10

And so with all workers who have the same score.

I would like to know if there is a better way to apply the query. Thanks in advance!

mysql query

asked by José Loguercio 15.06.2018 в 21:51

source

1 answer

Query with 3 tables Notice: Undefined offset: 0 in C: \ xampp \ htdocs \ www \ SERVICESUNARP \ index.php on line 57

score 4 · Accepted Answer

I will propose a solution based on JOIN .

Here you can see the test data that has been used: SEE DEMO IN REXTESTER .

For example, this query establishes two types of order:

DESC endente for the total points

ASC endente combining apellido,nombre

SELECT 
    e.apellido, 
    e.nombre, 
    SUM(p.points) puntos 
FROM employee_20180615 e 
INNER JOIN point_20180615 p
ON e.employee_id=p.employee_id
GROUP BY e.employee_id
ORDER BY puntos DESC, e.apellido,e.nombre ASC;

The result would be similar to:

apellido    nombre      puntos
Pérez       Juan        120
Cruzado     Ricardo     80
Medina      Carla       80
Medina      Carlos      80
Segura      Jesús       80

This other query establishes the order like this:

DESC endente for the total points

ASC endente combining nombre,apellido

SELECT 
    e.nombre, 
    e.apellido, 
    SUM(p.points) puntos 
FROM employee_20180615 e 
INNER JOIN point_20180615 p
ON e.employee_id=p.employee_id
GROUP BY e.employee_id
ORDER BY puntos DESC, e.nombre,e.apellido ASC;

The result would be more or less:

nombre      apellido    puntos
Juan        Pérez       120
Carla       Medina      80
Carlos      Medina      80
Jesús       Segura      80
Ricardo     Cruzado     80

As you can see, you can combine not only columns to participate in the same order, but you can also combine sequences of order, for example, you can ask that you order in descending order by column (s) X,Y and ascending by column (s) A,B .

Knowing that, you can order as best suits you.

I hope you find it useful.