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

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
surname | atractionname2| 1.1.2001 | 2.8.2021 will display

CodePudding user response：

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

CodePudding user response：

Try MAX(Pristupod)

MAX(Pristupod) will give you the newest record

CodePudding user response：

If the row_number function is available.

Select *
From
(
    Select 
      Zamestnanec.Prijmeni as Prijmeni
    , Atrakce.Nazev as nazev
    , Pristup.Pristupod as Od
    , Pristup.Pristupdo as Do
    , row_number() over (partition by Zamestnanec.Prijmeni, Atrakce.Nazev order by Pristup.Pristupod desc) as Rn
    From Zamestnanec
    Inner Join ZamestnaneckaKarta Karta
      On Karta.ZamestnanecID = Zamestnanec.ZamestnanecID
    Inner Join Pristup
      On Pristup.ZamestnaneckakartaID = Karta.ZamestnaneckakartaID
    Inner Join Terminal
      On Terminal.TerminalID = Pristup.TerminalID 
    Inner Join Atrakce
      On Atrakce.AtrakceID = Terminal.AtrakceID
) q
Where Rn = 1