[MySQL] query met MAX functie en koppelen naam en datum

[Fons Brand]

Ik maak een query op paar tabellen waarin gegevens taan m.b.t. datums van oefen avonden, scores en namen. (er staan meer gegevens in maar zijn nu niet van belang).

SQL:

select `vlakschieten`.`OefenID` AS `OefenID`,
    `vlakschieten`.`DatumID` AS `DatumID`,
    `vlakschieten`.`GildelidID` AS `GildelidID`,
    `vlakschieten`.`T` AS `T`,
    `vlakschieten`.`S` AS `S`,
    `vlakschieten`.`G` AS `G`,
    `vlakschieten`.`Rang` AS `Rang`,
    `datumoefening`.`Datum` AS `Datum`,
    `gildeleden`.`Naam` AS `Naam`
from (((`vlakschieten` join `datumoefening`) join `gildeleden`) join `seizoen`) 
where ((`vlakschieten`.`DatumID` = `datumoefening`.`DatumID`) 
    and (`vlakschieten`.`GildelidID` = `gildeleden`.`GildelidID`) 
    and (`seizoen`.`seizoenID` = `datumoefening`.`seizoenID`)) 
order by `datumoefening`.`Datum`

code:

OefenID    DatumID GildelidID  T    S       G       Rang    Datum       Naam        
    1       27      1           180 30      6       4       2010-08-04  Gerrit      
    2       27      2           166 30      5.53    5       2010-08-04  Herman  
    3       27      3           211 30      7.03    2       2010-08-04  Leo     
    4       27      5           213 30      7.1     1       2010-08-04  Fons        
    5       27      4           0   NULL    NULL    NULL    2010-08-04  Nico        
    6       27      6           0   NULL    NULL    NULL    2010-08-04  Walter      
    7       27      7           159 30      5.3     6       2010-08-04  Jan1        
    8       27      8           196 30      6.53    3       2010-08-04  Arie        
    9       27      9           0   NULL    NULL    NULL    2010-08-04  Jan2        
    10      27      10          0   NULL    NULL    NULL    2010-08-04  Ad      
    11      27      11          0   NULL    NULL    NULL    2010-08-04  Henk        
    384     27      13          0   NULL    NULL    NULL    2010-08-04  Gratia      
    424     27      12          0   NULL    NULL    NULL    2010-08-04  controle

Enz…
Al ik nu met onderstaande query van elke naam de maximale score opvraag en ook de datum wanneer deze is score is behaald , dan wordt bij de hoogste score niet de juiste datum weergegeven. De hoogste score per persoon word wel juist weergegeven.

SQL:

select `qryoefeningenvlak`.`Naam` AS `Naam`,
    `qryoefeningenvlak`.`Datum` AS `Datum`,
    max(`qryoefeningenvlak`.`T`) AS `MAX(T)` 
from `qryoefeningenvlak` 
group by `qryoefeningenvlak`.`Naam` 
order by max(`qryoefeningenvlak`.`T`) desc

code:

Naam        Datum       MAX(T) 
Arie        2010-08-04  272
Leo         2010-08-04  262
Fons        2010-08-04  261
Walter      2010-08-04  261
Jan2        2010-08-04  260
Gerrit      2010-08-04  256
Dik         2014-09-24  251
Jeroen      2013-10-02  244
Jan1        2010-08-04  240
Herman      2010-08-04  235
Kylian      2011-09-28  205
Nico        2010-08-04  197
Gratia      2010-08-25  183
Yvonne      2014-09-24  165
Ad          2010-08-04  159
Henk        2010-08-04  0
controle    2010-08-04  0

Met de onderstaande query wordt wel de juiste datum bij de hoogste score getoond maar ik heb maar ik heb nu alle data en niet 1 per persoon.

SQL:

select `qryoefeningenvlak`.`Naam` AS `Naam`,
    `qryoefeningenvlak`.`Datum` AS `Datum`, 
    max(`qryoefeningenvlak`.`T`) AS `MAX(T)` 
from `qryoefeningenvlak` 
group by `qryoefeningenvlak`.`Naam` , `qryoefeningenvlak`.`Datum`
order by max(`qryoefeningenvlak`.`T`) desc

code:

Naam      Datum        MAX(T) 
Arie      2012-09-26   272
Arie      2012-10-17   271
Arie      2011-11-23   270
Arie      2012-05-02   266
Arie      2012-09-19   265
Arie      2012-10-03   265
Arie      2012-04-18   264
Leo       2012-10-31   262
Arie      2014-02-05   262
Walter    2012-10-31   261
Arie      2013-02-06   261
Arie      2015-03-18   261
Fons      2015-04-01   261
Walter    2012-04-18   260
Jan2      2015-04-08   260
Walter    2012-02-08   259
Arie      2012-04-25   259
Fons      2013-11-27   259

Enz..

Kan iemand mij op weg helpen want ik draai rondjes....

[ Voor 81% gewijzigd door Fons Brand op 24-04-2015 07:55 ]

[RobIII]

Wil je voortaan bij 't posten van code (of "resultaten") code tags gebruiken a.u.b.?

Verder kan ik weinig hout snijden van je post; misschien is het handig als je je post nog eens naleest en beseft dat we 0 weet hebben van je project (wat is/bepaalt precies de "score"? ik zie dat nergens terug in je query danwel resultaten?) en zinnen als de volgende maken 't niet bepaald duidelijker: "Nu wil ik van elke naam de maximale score en op welke datum deze is score is behaald weten maar dan wordt." ...wat?

Inhoudelijk: ik vermoed dat je aan Hoe werkt dat GROUP BY nu eigenlijk? wel iets hebt...

[ Voor 13% gewijzigd door RobIII op 23-04-2015 23:47 ]

[Fons Brand]

@RobIII

Ik heb het stukje tekst herschreven.

De behhalde score staat in de tabel (T) en wordt niet berekend.
Ik heb niks aan group by want ik wil van die dubbele namen af. Van 1 naam de maximale (hoogste) score (T) zoals in de laatste query, maar wel met de juiste datum erbij.

[Caelorum]

Je wilt iets doen als

SQL:

select d.Naam, v.Datum, v.T
from vlakschieten v, datumoefening d
where v.DatumID = d.DatumID, 
group by d.Naam, v.Datum
having T = max(T)

*edit*
Of wil je van alle namen de hoogste score?
Want dan zou je het kunnen oplossen met iets als

SQL:

select d.Naam, 
      (select v.T from vlakschieten v, datumoefening d where v.DatumID = d.DatumID group by d.Naam, v.Datum having v.T = max(v.T)) T, 
      (select v.Datum from vlakschieten v, datumoefening d where v.DatumID = d.DatumID group by d.Naam, v.Datum having v.T = max(v.T)) Datum
from vlakschieten v
group by d.Naam

Zou vast ook wel beter kunnen

[ Voor 126% gewijzigd door Caelorum op 24-04-2015 10:08 ]

[raptorix]

Ik snap nog steeds wat je wilt, wellicht dat het werkt als je een sampleoutput geeft van wat je wil bereiken.

[Caelorum]

@raptorix, als ik zijn probleem met het laatste output zie dan wil hij:

code:

Naam      Datum        MAX(T) 
Arie      2012-09-26   272
Leo       2012-10-31   262
Walter    2012-10-31   261
Fons      2015-04-01   261
Jan2      2015-04-08   260

Dus per persoon de max score en de datum waarop die max score is behaald.

Misschien dat een group by naam, max(t) dan beter werkt dan group by naam, datum, maar ik ben momenteel ook niet meer helemaal helder

[ Voor 17% gewijzigd door Caelorum op 24-04-2015 10:40 ]

[mr_derk]

SQL:

with max_score as(
select naam, max(t) max_score
from vlakschieten
group by naam
)
select v.naam, max(v.datum) datum, m.max_score 
from vlakschieten v
, max_score m
where m.max_score = v.t
and m.naam = v.naam
group by v.naam, m.max_score

evt kan je ook min(v.datum) pakken als je de eerste datum van de max score wilt tonen. Weet niet of je twee keer je max score kan halen

Oh shit.. Mysql heeft geen WITH. Uitdaging voor jou om een alternatief te zoeken

[ Voor 39% gewijzigd door mr_derk op 24-04-2015 11:11 ]

[ajakkes]

Je zoekt naar GROUP BY want je wil van dubbele namen af.

Maar ter verduidelijking
Ik denk dat je hebt:

code:

Arie    5   1-5-2014
Arie 100 1-6-2014
Arie     3  1-7-2014
Kees 200 1-4-2014

En je wilt daar als resultaat van:

code:

Kees 200  1-4-2014
Arie   100  1-6-2014

In jouw voorbeeld bron mis ik dat namen vaker voorkomen en dat datums verschillend zijn. Zorg dus voor een beperkt duidelijk voorbeeld.

Fons Brand schreef op vrijdag 24 april 2015 @ 08:01:
@RobIII

Ik heb het stukje tekst herschreven.

De behhalde score staat in de tabel (T) en wordt niet berekend.
Ik heb niks aan group by want ik wil van die dubbele namen af. Van 1 naam de maximale (hoogste) score (T) zoals in de laatste query, maar wel met de juiste datum erbij.

[ Voor 29% gewijzigd door ajakkes op 24-04-2015 13:19 ]

[Caelorum]

Ik vermoed dat hij al genoeg voorbeelden en mogelijke oplossingen heeft gehad Ben benieuwd hoe hij het uiteindelijk heeft opgelost.

[NLChris]

In dit soort gevallen gebruik ik zelf vaak onderstaande aanpak (uit de losse pols):

SQL:

SELECT

  `vlakschieten`.`GildelidID` AS `GildelidID`,
  `datumoefening`.`Datum`     AS `Datum`,
  `gildeleden`.`Naam`         AS `Naam`,


  `vlakschieten`.`OefenID`    AS `OefenID`,
  `vlakschieten`.`DatumID`    AS `DatumID`,

  `vlakschieten`.`T`          AS `T`,
  `vlakschieten`.`S`          AS `S`,
  `vlakschieten`.`G`          AS `G`,
  `vlakschieten`.`Rang`       AS `Rang`

FROM `gildeleden`
  JOIN `vlakschieten` ON `vlakschieten`.`GildelidID` = `gildeleden`.`GildelidID`
  JOIN `datumoefening` ON `vlakschieten`.`DatumID` = `datumoefening`.`DatumID`
  JOIN `seizoen` ON `seizoen`.`seizoenID` = `datumoefening`.`seizoenID`

  LEFT JOIN vlakschieten beter_resultaat ON beter_resultaat.GildelidID = vlakschieten.GildelidID 
        AND beter_resultaat.T > vlakschieten.T

WHERE beter_resultaat.OefenID IS NULL

Waarbij je dus een oefening koppelt aan een beter resultaat, bestaat dit niet, dan heb je het maximale resultaat te pakken! Je kunt dit ook met een NOT EXISTS doen.

[Fons Brand]

De oplossing van NLChris is precies wat ik wilde, ik kreeg echter nog wel een aantal records met een NULL waarde maar na het veld T NULL naar 0 te hebben bij gewerkt is het in orde.

Allemaal ontzettend bedankt voor de reacties en de hulp om mij weer op de rit te krijgen.

[SQL]
SELECT

`vlakschieten`.`GildelidID` AS `GildelidID`,
`datumoefening`.`Datum` AS `Datum`,
`gildeleden`.`Naam` AS `Naam`,


`vlakschieten`.`OefenID` AS `OefenID`,
`vlakschieten`.`DatumID` AS `DatumID`,

`vlakschieten`.`T` AS `T`,
`vlakschieten`.`S` AS `S`,
`vlakschieten`.`G` AS `G`,
`vlakschieten`.`Rang` AS `Rang`

FROM `gildeleden`
JOIN `vlakschieten` ON `vlakschieten`.`GildelidID` = `gildeleden`.`GildelidID`
JOIN `datumoefening` ON `vlakschieten`.`DatumID` = `datumoefening`.`DatumID`
JOIN `seizoen` ON `seizoen`.`seizoenID` = `datumoefening`.`seizoenID`

LEFT JOIN vlakschieten beter_resultaat ON beter_resultaat.GildelidID = vlakschieten.GildelidID
AND beter_resultaat.T > vlakschieten.T

WHERE beter_resultaat.OefenID IS NULL
GROUP BY T DESC
[/SQL]