Imali smo tri kategorije muzicara i trazili smo one muzicare koji imaju drugu najvecu platu u svojoj kategoriji. U nekim kategorijama bilo je vise od jednog muzicara koji imaju drugu najvecu platu. U svim kategorijama, imali smo tacno jednog muzicara sa najvecom platom. Sada sam to malo promenio, tako da u nekim kategorijama imamo vise od jednog muzicara sa najvecom platom.
Dodao sam dva reda u tabelu #Zaposleni i na moje iznennadjenje, pola uspesnih resenja vise ne radi.
Ovo mi je slucajno palo na pamet, nisam imao nameru da se sluzim trikovima i navedem nikoga na 'pogresna' resenja. Sva resenja su bila dobra, za ponudjeni primer i i dalje ostaju dobra - za ponudjeni primer. Medjutim, neka od njih ne bi prezivela dugo u praksi, jer smo svi precutno i nesvesno svi napravili istu predpostavku - da ce podaci u praksi uvek imati tacno jednog muzicara sa najvecom platom. Sa nekim resenjima imali smo srece a sa nekim nismo, iako sva izgledaju podjednako dobra za zadate uslove.
Evo nove tabele sa novim ulaznim podacima:
IF Object_ID('tempdb..#Zaposleni') IS NOT NULL DROP TABLE #Zaposleni
;
CREATE TABLE #Zaposleni (
RadnikID INT IDENTITY,
Radnik VARCHAR(15),
RadnaJedinica VARCHAR(15),
Plata NUMERIC(16,2)
)
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('M Ilic','Narodnjaci', 40000)
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('D Zivkovic','Narodnjaci', 25000)
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('T Zdravkovic','Narodnjaci', 20000)
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('P Gojkovic','Narodnjaci', 15000)
;
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('G Bregovic','Rokeri', 80000)
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('Dz Stulic','Rokeri', 40000)
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('R M Tocak','Rokeri', 50000)
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('B Djordjevic','Rokeri', 50000)
;
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('D Zubovic','Klasicna', 25000)
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('J JOvanovic','Klasicna', 18000)
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('R Bakocevic','Klasicna', 15000)
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('O Njego','Klasicna', 10000)
;
-- Ovo smo dodali, sada kategorije 'Klasicna' i 'Rokeri' imaju po dva muzicara na vrhu liste
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('Z Saramandic','Klasicna', 25000)
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('Dz Stulic','Rokeri', 80000)
;
-- Ulazni podaci:
SELECT * FROM #Zaposleni
ORDER BY RadnaJedinica, Plata DESC
;
/* Ulazni podaci:
RadnikID Radnik RadnaJedinica Plata
----------- --------------- --------------- ---------------------------------------
9 D Zubovic Klasicna 25000.00
13 Z Saramandic Klasicna 25000.00
10 J JOvanovic Klasicna 18000.00
11 R Bakocevic Klasicna 15000.00
12 O Njego Klasicna 10000.00
1 M Ilic Narodnjaci 40000.00
2 D Zivkovic Narodnjaci 25000.00
3 T Zdravkovic Narodnjaci 20000.00
4 P Gojkovic Narodnjaci 15000.00
5 G Bregovic Rokeri 80000.00
14 Dz Stulic Rokeri 80000.00
7 R M Tocak Rokeri 50000.00
8 B Djordjevic Rokeri 50000.00
6 Dz Stulic Rokeri 40000.00
(14 row(s) affected)
*/
;
Ocekivani rezultat osto je isti:
-- Ocekivani rezultat:
/*
RadnaJedinica Radnik Plata
--------------- --------------- ---------------------------------------
Klasicna J JOvanovic 18000.00
Narodnjaci D Zivkovic 25000.00
Rokeri B Djordjevic 50000.00
Rokeri R M Tocak 50000.00
(4 row(s) affected)
*/
Testirao sam rezultat prve mozgalice i dobio ovo:
1. prvo resenje od mmix, bez rank funkcija, radi ispravno
-- MMIX 1, formatirano:
SELECT RadnaJedinica, Radnik,
Plata
FROM #Zaposleni
INNER JOIN
(SELECT RadnaJedinica AS RJ2, MAX(Plata) AS DrugaPlata
FROM #Zaposleni
INNER JOIN
(SELECT RadnaJedinica AS RJ, MAX(Plata) AS MaxPlata
FROM #Zaposleni
GROUP BY RadnaJedinica
)
AS TMaxPlata
ON #Zaposleni.RadnaJedinica = TMaxPlata.RJ
WHERE Plata < MaxPlata
GROUP BY RadnaJedinica
)
AS TSecPlata
ON #Zaposleni.RadnaJedinica = TSecPlata.RJ2
WHERE Plata = DrugaPlata
ORDER BY RadnaJedinica, Radnik;
RadnaJedinica Radnik Plata
--------------- --------------- ---------------------------------------
Klasicna J JOvanovic 18000.00
Narodnjaci D Zivkovic 25000.00
Rokeri B Djordjevic 50000.00
Rokeri R M Tocak 50000.00
(4 row(s) affected)
Drugo resenje od mmix, nazalost ne radi vise:
-- MMIX 2, formatirano:
SELECT RadnaJedinica,
Radnik ,
Plata
FROM #Zaposleni
INNER JOIN
(SELECT RadnaJedinica AS RJ ,
Plata AS rangPlata,
ROW_NUMBER() over (partition BY RadnaJedinica
ORDER BY Plata DESC) AS pindex
FROM #Zaposleni
)
AS TRPlate
ON #Zaposleni.RadnaJedinica = TRPlate.RJ
AND #Zaposleni.Plata = TRPlate.rangPlata
WHERE pindex = 2
ORDER BY RadnaJedinica,
Radnik;
RadnaJedinica Radnik Plata
--------------- --------------- ---------------------------------------
Klasicna D Zubovic 25000.00
Klasicna Z Saramandic 25000.00
Narodnjaci D Zivkovic 25000.00
Rokeri Dz Stulic 80000.00
Rokeri G Bregovic 80000.00
(5 row(s) affected)
Resenje koje je dao Vujkev je u sustini isto kao mmix drugo resenje, samo je upotrebljena WITH sintaksa umesto in-line SELECT, i naravno daje isti pogresan rezultat
--Vujkev, kao MMIX 2, upotreba WITH umesto in-line SQL, neformatirano, as-is
with e as (
select RadnaJedinica
, Plata
, row_number() over (partition by RadnaJedinica order by Plata desc) rb
from #Zaposleni
)
select z.RadnaJedinica, z.Radnik, z.Plata from e
inner join #Zaposleni z on z.Plata = e.Plata and z.RadnaJedinica = e.RadnaJedinica
where rb = 2
order by z.radnajedinica, z.radnik
;
RadnaJedinica Radnik Plata
--------------- --------------- ---------------------------------------
Klasicna D Zubovic 25000.00
Klasicna Z Saramandic 25000.00
Narodnjaci D Zivkovic 25000.00
Rokeri Dz Stulic 80000.00
Rokeri G Bregovic 80000.00
(5 row(s) affected)
mmix resenje za RANK(), ono najbrze, promasilo je sve slucajeve gde imamo vise od jednog muzicara na vrhu:
-- MMIx: resenje sa RANK()
WITH TRangirano AS
(
SELECT RadnaJedinica
, Radnik
, Plata
, RANK() over (partition BY RadnaJedinica ORDER BY Plata DESC) AS redPlate
FROM #Zaposleni
)
SELECT RadnaJedinica
, Radnik
, Plata
FROM TRangirano
WHERE redPlate = 2
ORDER BY RadnaJedinica
, Radnik;
RadnaJedinica Radnik Plata
--------------- --------------- ---------------------------------------
Narodnjaci D Zivkovic 25000.00
(1 row(s) affected)
Markovo resenje, ono koje je radilo u prvoj verziji mozgalice, nije proslo.
--- Najnovije Markovo resenje: (radilo u prvoj verziji pitalice)
select * from
(
select * from #Zaposleni where plata in
(select distinct(plata) from #Zaposleni where
( select count(*) from #Zaposleni as t where #Zaposleni.RadnaJedinica=t.RadnaJedinica and t.plata>#Zaposleni.plata)=1)
) p
where
(
select count(*) from
(
select * from #Zaposleni where plata in
(select distinct(plata) from #Zaposleni where
( select count(*) from #Zaposleni as t where #Zaposleni.RadnaJedinica=t.RadnaJedinica and t.plata>#Zaposleni.plata)=1)
) p1 where p.RadnaJedinica=p1.RadnaJedinica and p.plata>p1.plata
)=0
;
RadnikID Radnik RadnaJedinica Plata
----------- --------------- --------------- ---------------------------------------
2 D Zivkovic Narodnjaci 25000.00
9 D Zubovic Klasicna 25000.00
13 Z Saramandic Klasicna 25000.00
(3 row(s) affected)
ZIdarevo resenje a DENSE_RANK je proslo, ali sasvim slucajno, ja jednostavno nisam umeo da napsiem resenje sa RANK pa sam napsiao sa DENSE_RANK.
-- Zidar:
;
WITH RankByRadnaJedinica AS
( SELECT DISTINCT
RadnaJedinica ,
Plata ,
PlataOrder = dense_rank() OVER (PARTITION BY RadnaJedinica ORDER BY Plata DESC)
FROM #Zaposleni
)
SELECT E.RadnikID ,
E.Radnik ,
E.RadnaJedinica,
E.Plata
FROM #Zaposleni AS E
JOIN RankByRadnaJedinica AS R
ON R.RadnaJedinica = E.RadnaJedinica
AND R.Plata = E.Plata
WHERE R.PlataOrder = 2
ORDER BY RadnaJedinica,
E.Radnik ;
RadnikID Radnik RadnaJedinica Plata
----------- --------------- --------------- ---------------------------------------
10 J JOvanovic Klasicna 18000.00
2 D Zivkovic Narodnjaci 25000.00
8 B Djordjevic Rokeri 50000.00
7 R M Tocak Rokeri 50000.00
(4 row(s) affected)
Verovatno se resenja koja nisu prosla mogu naterati da prorade, ali to nije poenta. Poenta je da je dobro postavljeno pitanje pola resenja. Inicijalno pitanje je bilo postavljeno lose. Ulazni podaci su (nenamerno) napravljeni tako da mogu da prodju i prividno tacna resenja. Ovo samo potvrdjuje satru istinu - testirati, testirati i samo testirati, za sto vise slucajeva. Da je na pocetku dat bolji skup podataka za primer, i resenja bi bila bolja.