Zwracanie TOP1 danej Grupy w Access SQL

Siema. Chciałbym się zapytać jak w Access SQL zwrócić TOP1 danej grupy. Mam taki kod:

SELECT imie, nazwisko, rok, Sum(punkty)
FROM wyniki, kierowcy, wyscigi
WHERE wyscigi.id_wyscigu=wyniki.id_wyscigu AND wyniki.id_kierowcy=kierowcy.id_kierowcy
GROUP BY imie, nazwisko, rok
HAVING rok LIKE 2000 OR rok LIKE 2006 OR rok LIKE 2010
ORDER BY rok DESC , sum(punkty) DESC;

Pogrupowałem tak że dla każdego zawodnika (imie, nazwisko) z lat 2000,2006,2010 jest wyliczona suma punktów jakie w tych latach uzyskał. Lata posortowane są malejąco, tak samo jak punkty. Oczywiście mogę odpalić kwerendę i odczytać sobie samemu TOP 1 z danego roku i pewnie byłoby szybciej, ale ciekawi mnie jak wyciągnąć pierwszy wiersz dla każdego roku?

Zadanie jest z matury 2015, a dokładnie 6.3. Link do arkusza:

cke.gov.pl/images/_EGZAMIN_MATURALNY_OD_2015/Arkusze_egzaminacyjne/2015/formula_od_2015/MIN-R2_1P-152.pdf

SELECT TOP 1 imie, nazwisko, rok, Sum(punkty)
FROM wyniki, kierowcy, wyscigi
WHERE wyscigi.id_wyscigu=wyniki.id_wyscigu AND wyniki.id_kierowcy=kierowcy.id_kierowcy
GROUP BY imie, nazwisko, rok
HAVING rok LIKE 2000 OR rok LIKE 2006 OR rok LIKE 2010
ORDER BY rok DESC , sum(punkty) DESC;

https://www.w3schools.com/sqL/sql_top.asp

Nie ma gotowej składni do osiągnięcia tego, musiałbyś najpierw policzyć punkty funkcją sum, później użyć max do wyciagnięcia wartości największych w danym roku i z joinować ze sobą by wyciągnąć pierwszych w danym sezonie, jak dasz jakieś dane to Ci to mogę napisać, bo z głowy w Accessowej składni nie napiszę...

Tu jednak poszedłbym na łatwiznę i uzył uniona:

SELECT 
     imie
     , nazwisko
     , rok
     , Sum(punkty)
FROM 
    wyniki, kierowcy, wyscigi
WHERE 
    wyscigi.id_wyscigu=wyniki.id_wyscigu 
    AND wyniki.id_kierowcy=kierowcy.id_kierowcy
    AND ROK = 2000
GROUP BY 
    imie
   , nazwisko
   , rok
ORDER BY 
     sum(punkty) DESC
union 
SELECT 
     imie
     , nazwisko
     , rok
     , Sum(punkty)
FROM 
    wyniki, kierowcy, wyscigi
WHERE 
    wyscigi.id_wyscigu=wyniki.id_wyscigu 
    AND wyniki.id_kierowcy=kierowcy.id_kierowcy
    AND ROK = 2006
GROUP BY 
    imie
   , nazwisko
   , rok
ORDER BY 
     sum(punkty) DESC
union
SELECT 
     imie
     , nazwisko
     , rok
     , Sum(punkty)
FROM 
    wyniki, kierowcy, wyscigi
WHERE 
    wyscigi.id_wyscigu=wyniki.id_wyscigu 
    AND wyniki.id_kierowcy=kierowcy.id_kierowcy
    AND ROK = 2012
GROUP BY 
    imie
   , nazwisko
   , rok
ORDER BY 
     sum(punkty) DESC

Wiele rozjaśniło mi to. W twoim kodzie jest problem, że order by nie można umieścić w każdym pytaniu tylko na końcu. Zrobiłem coś takiego:

SELECT TOP 1  imie, nazwisko, rok, Sum(punkty)
FROM wyniki, kierowcy, wyscigi
WHERE wyscigi.id_wyscigu=wyniki.id_wyscigu AND wyniki.id_kierowcy=kierowcy.id_kierowcy
GROUP BY imie, nazwisko, rok
HAVING rok LIKE 2010


union

SELECT TOP 1 imie, nazwisko, rok,  Sum(punkty)
FROM wyniki, kierowcy, wyscigi
WHERE wyscigi.id_wyscigu=wyniki.id_wyscigu AND wyniki.id_kierowcy=kierowcy.id_kierowcy
GROUP BY imie, nazwisko, rok
HAVING  rok LIKE 2006


union 

SELECT TOP 1 imie, nazwisko, rok, sum(punkty)
FROM wyniki, kierowcy, wyscigi
WHERE wyscigi.id_wyscigu=wyniki.id_wyscigu AND wyniki.id_kierowcy=kierowcy.id_kierowcy
GROUP BY imie, nazwisko, rok
HAVING rok LIKE 2000
ORDER BY 3 DESC , 4 DESC

Wszystko było by fajnie, tylko najpierw bierze TOP 1, a dopiero potem grupuje po roku i sumie punktów :/ Wiece może jak to obejść?

Tutaj link do pliku access

https://files.fm/u/b362fxuv#_

Ach ten access ;)

select * from  (
SELECT top 1
     imie
     , nazwisko
     , rok
     , SUM(punkty)
FROM 
    wyniki, kierowcy, wyscigi
WHERE 
    wyscigi.id_wyscigu=wyniki.id_wyscigu 
    AND wyniki.id_kierowcy=kierowcy.id_kierowcy
    AND ROK = 2000
GROUP BY 
    imie
   , nazwisko
   , rok
ORDER BY 
     SUM(punkty) DESC) as d1
UNION 
select * from (
SELECT top 1
     imie
     , nazwisko
     , rok
     , SUM(punkty)
FROM 
    wyniki, kierowcy, wyscigi
WHERE 
    wyscigi.id_wyscigu=wyniki.id_wyscigu 
    AND wyniki.id_kierowcy=kierowcy.id_kierowcy
    AND ROK = 2006
GROUP BY 
    imie
   , nazwisko
   , rok
ORDER BY 
     SUM(punkty) DESC) as d2
UNION
select * from (
SELECT top 1
     imie
     , nazwisko
     , rok
     , SUM(punkty)
FROM 
    wyniki, kierowcy, wyscigi
WHERE 
    wyscigi.id_wyscigu=wyniki.id_wyscigu 
    AND wyniki.id_kierowcy=kierowcy.id_kierowcy
    AND ROK = 2012
GROUP BY 
    imie
   , nazwisko
   , rok
ORDER BY 
     SUM(punkty) DESC) as d3

To przejdzie

Dzięki wielkie. Nie wiedziałem że trzeba to aż tak przekombinować :/ To taki problem tylko w access jest?

Skoro dałeś wersje z danymi, to masz rozwiązanie bez uniona:

SELECT i.Rok,
       Kierowcy.Imie,
       Kierowcy.Nazwisko,
       Kierowcy.Kraj,
       i.pkt
FROM(
(
    SELECT Rok,
           Id_kierowcy,
           SUM(Punkty) AS pkt
    FROM Wyniki
         INNER JOIN Wyscigi ON Wyniki.Id_wyscigu = Wyscigi.Id_wyscigu
    GROUP BY rok,
             id_kierowcy
) AS i
INNER JOIN
(
    SELECT rok,
           MAX(pkt) AS pm
    FROM
(
    SELECT Rok,
           Id_kierowcy,
           SUM(Punkty) AS pkt
    FROM Wyniki
         INNER JOIN Wyscigi ON Wyniki.Id_wyscigu = Wyscigi.Id_wyscigu
    GROUP BY rok,
             id_kierowcy
)
    GROUP BY rok
) AS m ON(i.pkt = m.pm)
         AND (i.rok = m.rok))
INNER JOIN Kierowcy ON i.Id_kierowcy = Kierowcy.Id_kierowcy
WHERE i.rok IN(2000, 2006, 2012);

W innych bazach masz inne możliwości jak numer wiersza, który to bardzo ułatwia i wtedy to samo osiągasz tak:

select
    imie,
    nazwisko,
    rok,
    pkt
from (
    select
	   *
	   ,row_number() over (partition by rok order by pkt desc) r
    from (select
		  k.imie
		  ,k.nazwisko
		  ,gp.rok
		  ,sum(punkty) pkt
	   from 
		  kierowcy k
		  inner join wyniki w on k.id_kierowcy = w. id_kierowcy
		  inner join wyscigi gp on gp.id_wyscigu=w.id_wyscigu
	   group by  
		  k.imie
		  ,k.nazwisko
		  ,gp.rok) dt
    ) rs
where
    r=1
    and rok in (2000,2006,2012)

Trochę bardziej to pokomplikowane, a nie wiele krótsze. Będę musiał to powoli przeanalizować bo to dopiero początki z sql.

Nie miało być krótsze... Bez imiona łatwiej zmienić warunek na rok, niż zmieniać ilość podzapytań.

Trochę nie rozumiem tego po dłuższej analizie. Zaczynając od końca:

SELECT Rok, Id_kierowcy,  SUM(Punkty) AS pkt
FROM Wyniki          
INNER JOIN Wyscigi ON Wyniki.Id_wyscigu = Wyscigi.Id_wyscigu          
GROUP BY rok, id_kierowcy     
    

Bierzemy rok, id_kierowcy, suma z punktów łączymy dwie tabele wyniki i wyscigi na podstawie id wyscigu i grupujemy przez rok i id_kierowcy.

(
 SELECT rok,   MAX(pkt) AS pm
 FROM

(
SELECT Rok, Id_kierowcy,  SUM(Punkty) AS pkt
FROM Wyniki          
INNER JOIN Wyscigi ON Wyniki.Id_wyscigu = Wyscigi.Id_wyscigu          
GROUP BY rok, id_kierowcy     
    
            
)   GROUP BY rok

Z tej tabeli co przed chwilą opisałem bierzemy rok i największą wartość punktów, nazywamy ją jako "pkt" i grupujemy po roku. Aktualnie mamy wszystkie lata i wartość z maksymalną ilością punktów dla nich.

(
  SELECT Rok,   Id_kierowcy,    SUM(Punkty) AS pkt
  FROM Wyniki
  INNER JOIN Wyscigi ON Wyniki.Id_wyscigu = Wyscigi.Id_wyscigu       
  GROUP BY rok, id_kierowcy
) AS i   
  
         
INNER JOIN

Do tej tabeli co wyżej opisałem dołączamy tą z której wyciągamy rok, id_kierowcy i sume punktów grupujemy po roku i id i zapisujemy ją jako "i". Tutaj trochę nie rozumiem bo przecież wcześniej taką samą tabele stworzyliśmy i wyciągnęliśmy z niej rok i max pkt. Chodzi o to że w kolejnym punkcie wyciągamy imie, nazwisko itd. na podstawie tej tabeli z id?

AS m ON(i.pkt = m.pm)    AND (i.rok = m.rok))
INNER JOIN Kierowcy ON i.Id_kierowcy = Kierowcy.Id_kierowcy
WHERE i.rok IN(2000, 2006, 2012);

Trochę nie czaje tego

AS m ON(i.pkt = m.pm)    AND (i.rok = m.rok))

Nie spotkałem się z takim zapisem jeszcze bo to w sumie pierwszy tydzie nauki. Prosiłbym o drobna pomoc.

Dobrze kombinujesz. Podzapytanie i bierze indywidualne sumy punktów w ramach roku dla kierowcy. Podzapytanie m bierze maksymalną ilość punktów w ramach roku. Najważniejszy jest warunek po którym łączymy ze sobą te 2 podzapytanie, po dwóch kolumnach:rok i ilość punktów. To powoduje, że dostaniemy w wyniku tylko kierowców z maksymalną ilością punktów.
Na przykładzie, jeżeli i ma w 3 kierowców w roku 2000 z sumami punktów 1,2,3 to m będzie miało jeden zapis z rokiem 2000 z wartością 3. To połączenie tabel zwróci tylko kierowcę z ilością 3 punktów.
To służy do wyciągnięcia kierowców z największą ilością punktów w ramach roku.

Dobra już czaje wszytko. Źle spojrzałem na nawiasy i dlatego nic mi się nie zgadzało. A te inner joiny można zastąpić WHERE. Chodzi mi o te

 INNER JOIN Wyscigi ON Wyniki.Id_wyscigu = Wyscigi.Id_wyscigu  

Tylko wtedy do from trzeba przenieść wyscigi. Nie stosuje się tego z jakiegoś względu, czy tylko dlatego że gorzej się to czyta?

Prosta zasada - do łączenia JOIN, do filtrowania WHERE.

Jak pisał Marcin taki zapis inner joina jest nieczytelny, szczególnie jak dochodzą inne warunki. To podstawa, żeby podczas analizowania zapytania widzieć od razu po czym łączone są tabele, a po czym filtrowane