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

I want to select the latest distict from the first column (FirmaTransportowa.dbo.Kurier.id) I want to keep first two rows: — Kipero, Jan 19 '21 at 22:51
Try to combine your query with what is found here, 'https://stackoverflow.com/questions/11064473/select-the-first-instance-of-a-record' — Rajesh G, Jan 19 '21 at 22:56
@DaleK Yes, this is my definition. I want to get "first" 2 rows with the Max value in the third column :) — Kipero, Jan 19 '21 at 23:07

Dale K · Accepted Answer · 2021-01-19T23:24:56.333

0

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.

edited Jan 19 '21 at 23:24

answered Jan 19 '21 at 23:11

Dale K

25,246
15
42
71

Yea, This is a much easier query. But I have following errors: Msg 207, Level 16, State 1, Line 11 Invalid column name 'RowNum'. Msg 207, Level 16, State 1, Line 10 Invalid column name 'Col1'. Msg 207, Level 16, State 1, Line 10 Invalid column name 'Col2'. Msg 207, Level 16, State 1, Line 10 Invalid column name 'Col3'. – Kipero Jan 19 '21 at 23:16
@Kipero sorry missed the `from` - see edit. – Dale K Jan 19 '21 at 23:17
Wow man this :) I've had almost perfect result. I've got 2 rows but with the minimum value of third column. How can I solve it to the maximum? – Kipero Jan 19 '21 at 23:24
@Kipero check the docs for the row_number function and teach yourself how it works and then modify it to give you the correct row. Better you start teaching yourself at this stage :) – Dale K Jan 19 '21 at 23:25

score -1 · Answer 2 · edited Jan 20 '21 at 10:11

-1

This should work :)

select FirmaTransportowa.dbo.Kurier.id, FirmaTransportowa.dbo.Zlecenie.adresKoncowy, max(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 
Group by FirmaTransportowa.dbo.Kurier.id,FirmaTransportowa.dbo.Zlecenie.adresKoncowy
order by FirmaTransportowa.dbo.ZlecenieKurier.id

edited Jan 20 '21 at 10:11

majkrzak

1,332
3
14
30

answered Jan 19 '21 at 22:55

Ivan

1

3

Woo! What a mess! Please check out the code formatting option. – Dale K Jan 19 '21 at 22:56
Sorry for the messy code. Are you looking for the biggest FirmaTransportowa.dbo.ZlecenieKurier.id and FirmaTransportowa.dbo.Zlecenie.adresKoncowy for FirmaTransportowa.dbo.Kurier.id ? – Ivan Jan 19 '21 at 22:58
Thank you for your response but I got following error: Column "FirmaTransportowa.dbo.ZlecenieKurier.id" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause – Kipero Jan 19 '21 at 23:00
You can remove it. You dont need it anymore, the max() function replace it :) – Ivan Jan 19 '21 at 23:01
2

@Ivan for a good answer, please provide some explanation of what you are doing rather than just a (messy) code dump. That way the OP learns how to do it themselves. – Dale K Jan 19 '21 at 23:13

Select distinct the latest rows

2 Answers2