Too many records... I can't find a solution

  • Thread starter Thread starter Vicente Rodriguez Eguibar
  • Start date Start date
V

Vicente Rodriguez Eguibar

Hi group,

Last post wasn't succesfully, maby because I didn't show any "effort" trying
to reach my goal.

I'm trying to get a list with the highest average from each player, but when
I include the date the result becomes too long and each player is showed
several times with his highest average each date. I'm becoming crazy and
can't find the solution. This is the most I can get:

SELECT Jugadores.FirstName, Equipos.Nom_Equipo, Fechas.Fecha_jornada,
Max(Promedios.Prom_Jugador) AS MaxOfProm_Jugador
FROM Equipos INNER JOIN (Fechas INNER JOIN (Jugadores INNER JOIN Promedios
ON Jugadores.ID_Jugador = Promedios.ID_Jugador) ON Fechas.ID_Fecha =
Promedios.ID_Fecha) ON Equipos.ID_Equipos = Jugadores.ID_Equipos
GROUP BY Jugadores.FirstName, Equipos.Nom_Equipo, Fechas.Fecha_jornada
ORDER BY Max(Promedios.Prom_Jugador) DESC;

If I remove the "Fechas.Fecha_Jornada" (Dates.Date_Played) field I get
exactly what I need. Please help me, I'm starting to get fustrated.

Thanks in advance.

Vicente Rodriguez Eguibar
MCSE #31507
 
Vicente Rodriguez Eguibar said:
Hi group,

Last post wasn't succesfully, maby because I didn't show any "effort" trying
to reach my goal.

I'm trying to get a list with the highest average from each player, but when
I include the date the result becomes too long and each player is showed
several times with his highest average each date. I'm becoming crazy and
Click to expand...

If you want each players highest average each date, that would require
there to be more than one average per day. What divides up the day? Games
Played?

can't find the solution. This is the most I can get:

SELECT Jugadores.FirstName, Equipos.Nom_Equipo, Fechas.Fecha_jornada,
Max(Promedios.Prom_Jugador) AS MaxOfProm_Jugador
FROM Equipos INNER JOIN (Fechas INNER JOIN (Jugadores INNER JOIN Promedios
ON Jugadores.ID_Jugador = Promedios.ID_Jugador) ON Fechas.ID_Fecha =
Promedios.ID_Fecha) ON Equipos.ID_Equipos = Jugadores.ID_Equipos
GROUP BY Jugadores.FirstName, Equipos.Nom_Equipo, Fechas.Fecha_jornada
ORDER BY Max(Promedios.Prom_Jugador) DESC;

If I remove the "Fechas.Fecha_Jornada" (Dates.Date_Played) field I get
exactly what I need. Please help me, I'm starting to get fustrated.
Click to expand...

I have trouble understanding the problem.

When you include "Fechas.Fecha_Jornada", a problem happens (it gets too
long).

When you remove "Fechas.Fecha_Jornada", you get exactly what you need.

Answer: Do not include "Fechas.Fecha_Jornada".
 
Hi Chris2

Average is calculated every day based on 3 matches, so only one average per
day, but what I'm looking for is the highest average for each player
(Bowler) regarding the date, but in the result has to be shown when was
achived. I already thought in not showing the dates, but the regular bowlers
who check the page ask for the date... so not showing the date is not an
option.

Thanks for your comments. Any other ideas?
 
Vicente Rodriguez Eguibar said:
Hi Chris2

Average is calculated every day based on 3 matches, so only one average per
day, but what I'm looking for is the highest average for each player
(Bowler) regarding the date, but in the result has to be shown when was
achived. I already thought in not showing the dates, but the regular bowlers
who check the page ask for the date... so not showing the date is not an
option.

Thanks for your comments. Any other ideas?
Click to expand...

I was not sure what was going one.

Now, it is stated that there is are three matches per day.

It is also stated that an average is calculated for all three matches on
each day, so that there is only one average per day.

Since there is only one average per day, it is already the highest average
on that day, and so no MAX function is needed.


If you need the highest (MAX) average among *all* dates, then the dates
are rolled up, and no date can be shown, that is the definition of GROUPing.

Sincerely,

Chris O.
 
Back
Top