sql - Select distinct the latest rows

Question

How can I select the latest rows not duplicated?

select distinct FirmaTransportowa.dbo.Kurier.id, FirmaTransportowa.dbo.Zlecenie.adresKoncowy, FirmaTransportowa.dbo.ZlecenieKurier.id
from FirmaTransportowa.dbo.Kurier 
join FirmaTransportowa.dbo.ZlecenieKurier on FirmaTransportowa.dbo.Kurier.id= FirmaTransportowa.dbo.ZlecenieKurier.kurierId
join FirmaTransportowa.dbo.Zlecenie on FirmaTransportowa.dbo.Zlecenie.id= FirmaTransportowa.dbo.ZlecenieKurier.zlecenieId
where FirmaTransportowa.dbo.Kurier.id = FirmaTransportowa.dbo.ZlecenieKurier.kurierId 
order by FirmaTransportowa.dbo.ZlecenieKurier.id desc

Here is my result from the above:

3   Gdynia      6
2   Katowice    5
2   Gdynia      4
2   Gdynia      3
2   Poznań      2
3   Warszawa    1

I want to keep first two rows of the above, where first is defined as having the maximum value in column 3:

3   Gdynia      6
2   Katowice    5

Answer 1

Firstly, let me introduce you to table aliases - see how much easier your query is to read.

Now you can solve your problem using the row_number() function and then only selecting row numbers 1.

with cte as (
  select K.id Col1, Z.adresKoncowy Col2, ZK.id Col3
    , row_number() over (partition by K.id order by ZK.id desc) RowNum
  from FirmaTransportowa.dbo.Kurier K 
  join FirmaTransportowa.dbo.ZlecenieKurier ZK on K.id = ZK.kurierId
  join FirmaTransportowa.dbo.Zlecenie Z on Z.id = ZK.zlecenieId
  where K.id = ZK.kurierId 
  -- order by ZK.id desc
)
select Col1, Col2, Col3
from cte
where RowNum = 1
order by Col1 desc;

Note: Do use better column aliases than I have - I don't know what your columns represent but you do.