Zwracanie TOP1 danej Grupy w Access SQL

brus97

2018-03-29T00:33:24+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 66

0

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

Kopiuj

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

caderda

2018-03-29T01:25:04+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 12

0

Kopiuj

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

Panczo

2018-03-29T11:26:56+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 3891

1

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:

Kopiuj

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

brus97

2018-03-29T14:38:58+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 66

0

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:

Kopiuj

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#_

Panczo

2018-03-29T14:49:11+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 3891

1

Ach ten access ;)

Kopiuj

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

brus97

2018-03-29T14:53:14+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 66

0

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

Panczo

2018-03-29T15:27:39+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 3891

1

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

Kopiuj

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:

Kopiuj

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)

brus97

2018-03-29T15:40:46+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 66

0

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

Panczo

2018-03-29T20:19:24+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 3891

0

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

brus97

2018-03-31T00:03:08+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 66

0

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

Kopiuj

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.

Kopiuj

(
 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.

Kopiuj

(
  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?

Kopiuj

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

Kopiuj

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.

Panczo

2018-03-31T09:26:00+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 3891

1

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.

brus97

2018-03-31T15:46:52+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 66

0

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

Kopiuj

 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?

Marcin.Miga

2018-03-31T18:52:40+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 2794

2

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

Panczo

2018-04-01T20:30:15+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 3891

0

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