MS SQL managment studio – how to SELECT latest date FROM one table for each record in another table

i need to know how do i select newest record from table based on date for each record from another table.

Select 
Zamestnanec.Prijmeni Prijmeni, Atrakce.Nazev nazev, Pristup.Pristupod Od, Pristup.Pristupdo Do
from Zamestnanec, Atrakce, Pristup,ZamestnaneckaKarta, Terminal
where Zamestnanec.ZamestnanecID = ZamestnaneckaKarta.ZamestnanecID
and ZamestnaneckaKarta.ZamestnaneckakartaID = Pristup.ZamestnaneckakartaID
and Pristup.TerminalID = Terminal.TerminalID
and Terminal.AtrakceID =Atrakce.AtrakceID

when ZamestnaneckakartaID has more records in Pristup, i want to display only the newest one in column Pristupod

Result should looks like:

surname | atractionname | 1.1.2000 | 2.1.2000 will not display
surname2| atractionname2| 3.5.2000 | 4.5.2000
surname | atractionname | 2.6.2000 | 2.7.2000 will display

>Solution :

You can achieve this using window functions. Here is an example:

;WITH cte AS
(
    SELECT 
      Zamestnanec.Prijmeni AS Prijmeni, 
      Atrakce.Nazev AS nazev, 
      Pristup.Pristupod AS Od, 
      Pristup.Pristupdo AS Do,
      ROW_NUMBER() OVER(PARTITION BY ZK.ZamestnaneckakartaID ORDER BY P.Pristupod DESC) AS RN
    FROM Zamestnanec Z 
    JOIN ZamestnaneckaKarta ZK 
      ON Z.ZamestnanecID = ZK.ZamestnanecID
    JOIN Pristup P
      ON ZK.ZamestnaneckakartaID = P.ZamestnaneckakartaID
    JOIN Terminal T 
      ON P.TerminalID = T.TerminalID
    JOIN Atrakce A
      ON T.AtrakceID = A.AtrakceID
)
SELECT *
FROM cte 
WHERE RN = 1