how to bring ten best averages in mysql?

Navigation
0

I have two tables: participants and results. In the table of participants I have idParticipant as pk and name; in the results table I have idParticipant as fk, result and date.

For the result to enter the ranking the participants must inform 3 results per day (date field) but can inform less (client rule). Each day will have an average totaling the three results and dividing by three. In the end, the bank will have 15 days of each participant, but I need to bring the 10 best (lowest) averages of each participant. Remembering that you can only count the days that the participant submitted three results.

I made the query below that brings all the averages on the date that the participant submitted three results, but wanted a way to list only 10 days instead of all. Is it possible or am I going to have to return all the lines and eliminate the worst by PHP? 

 SELECT p.idParticipante, p.nome, g.data,
 ROUND(SUM( g.resultado /3),1) AS media,
 COUNT( g.resultado ) AS participacoes
 FROM participantes p
 JOIN resultados g ON p.idParticipante = g.idParticipante
 GROUP BY p.idParticipante, g.data
 HAVING participacoes = 3
 ORDER BY p.idParticipante, media
    
asked by anonymous 23.07.2017 / 06:05

1 answer

0

To list only the last 10 days, you can use the DATE_ADD function to subtract 10 days from the current date and compare with the date of your registration. Your query would look like this: 

SELECT p.idParticipante, p.nome, g.data,
ROUND(SUM( g.resultado /3),1) AS media,
COUNT( g.resultado ) AS participacoes
FROM participantes p
JOIN resultados g ON p.idParticipante = g.idParticipante
WHERE g.data >= DATE_ADD(NOW(), INTERVAL -10 DAY)
GROUP BY p.idParticipante, g.data
HAVING participacoes = 3
ORDER BY p.idParticipante, media
    
23.07.2017 / 14:10
Fragments and how to pass activity information to them Program expect response ajax request?