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

Question

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

What database do you use, and can you provide a sample of your data? — Bongo, Dec 17 '21 at 16:34

score 0 · Answer 1 · answered Dec 17 '21 at 16:25

0

Try MAX(Pristupod)

MAX(Pristupod) will give you the newest record

answered Dec 17 '21 at 16:25

Idriss

617
1
4
14

And where should i use it ? I have just this select – Dominik Kolouch Dec 17 '21 at 16:29
this will give a n idea of using it with select https://stackoverflow.com/questions/189213/sql-selecting-rows-by-most-recent-date-with-two-unique-columns – Idriss Dec 17 '21 at 16:31
same result Select Zamestnanec.Prijmeni Prijmeni, Atrakce.Nazev nazev, MAX(Pristup.Pristupod) as 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 group by Prijmeni, Nazev, Pristupdo – Dominik Kolouch Dec 17 '21 at 16:36

score 0 · Answer 2 · answered Dec 17 '21 at 16:36

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

score 0 · Accepted Answer · answered Dec 17 '21 at 16:38

0

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

answered Dec 17 '21 at 16:38

GoonerForLife

631
2
5

Thank you a lot =) I just had to edit Atrackce.Nazev to A.Nazev and its works. – Dominik Kolouch Dec 17 '21 at 16:46
One more question, this solution is just for newest record, but i want to newest record for each atraction. So newest for person1 on atraction1 and for person1 on atracion2 – Dominik Kolouch Dec 17 '21 at 16:52
The only thing you need to do is modify the Partitioning fields according to your requirement. Assuming you have atraction field in one of your tables, you need to do something like this ROW_NUMBER() OVER(PARTITION BY ZK.atraction, ZK.ZamestnaneckakartaID ORDER BY P.Pristupod DESC) AS RN – GoonerForLife Dec 17 '21 at 17:10
A.Atraction will add colums with expired dates, that i dont want to, i want to just newest – Dominik Kolouch Dec 17 '21 at 17:20
Can you post some sample data? – GoonerForLife Dec 17 '21 at 17:30
yeah of course https://ctrlv.cz/QYSk – Dominik Kolouch Dec 17 '21 at 17:40
The screenshot didn't help much. Try this one after replacing the fields in the partition by clause with the correct field names and see if it works. ROW_NUMBER() OVER(PARTITION BY ZK.Person, A.atraction, ZK.ZamestnaneckakartaID ORDER BY P.Pristupod DESC) – GoonerForLife Dec 17 '21 at 17:47
Thank you, but it not working. well, i can send u whole database with data – Dominik Kolouch Dec 17 '21 at 17:57
I have seen in your reply to one of the answers that you are trying to group by Prijmeni and Nazev fields. If that is the case, then those should be your partitioning fields. Try these two and see if you can get the result you are expecting. PARTITION BY Prijmeni , Nazev, ORDER BY P.Pristupod DESC --or PARTITION BY rijmeni , Nazev, ZK.ZamestnaneckakartaID ORDER BY P.Pristupod DESC – GoonerForLife Dec 17 '21 at 18:06

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

3 Answers3