---- Extremy a poradia
----	- ALL
----	- MAX, MIN
[----	- TOP(n) ... ORDER BY			]
[--		- ROW_NUMBER() OVER(ORDER BY ...]

USE Poliklinika;
GO

-- E0)Usporiadajte lekarov podla veku zostupne:
SELECT datNar FROM Lekari
	ORDER BY datNar DESC;	-- 5r: 1980//1970//1961//1960//1956

-- E1a) Najdite datum narodenia najmladsieho/u lekara/ku:
SELECT MAX(datNar) najmladsi FROM Lekari;

-- E1b) Vypiste aj jeho/jej krstne a specializaciu:
-- NO, lebo napr. krstne by mal byt BUD v AGR.FUNK. ALEBO V GROUP BY:
SELECT krstne, spec, MAX(datNar) najmladsi FROM Lekari;

-- NO:
SELECT krstne, spec, MAX(datNar) najmladsi FROM Lekari
	GROUP BY krstne, spec	-- 5r

-- OK:
SELECT krstne, spec, L2.datNar FROM Lekari L2
	WHERE L2.datNar =
		( SELECT MAX(L1.datNar) FROM Lekari L1 )

-- E1c) Riesme ulohu bez pouzitia MAX:
SELECT krstne, spec, L2.datNar FROM Lekari L2
	WHERE L2.datNar >= ALL  -- podmienka pre KAZDU/ALL DVOJICU!
		( SELECT L1.datNar FROM Lekari L1 )



-- E2) Najdite datum narodenia druheho najmladsieho/u lekara/ku:
SELECT MAX(L2.datNar) FROM Lekari L2
	WHERE L2.datNar <
		( SELECT MAX(L1.datNar) FROM Lekari L1 )

-- E3) Najdite vsetky udaje o tretom/tej najmladsom/ej lekarovi/ke:
SELECT * FROM Lekari L3 
	WHERE L3.datNar = (
		SELECT MAX(L2.datNar) FROM Lekari L2
			WHERE L2.datNar < (
				SELECT MAX(L1.datNar) FROM Lekari L1
				)
		)