SQL :Select unique/distinct rows based on the other column.

Question

Hi Team - I am trying to return the rows below the scenario

SQL 2017:

create table TableK
(
oldID int,
newid int,
oldname varchar(20),
newname varchar(20),
oldstreet varchar(20),
newstreet varchar(20),
lastupdatedt datetime

)

insert into TableK values
(1619,1619,'Eva','Zia','Broadst','lombardst','02/10/2021 13:57:00'),
(1619,1619,'Eva','Zia','Broadst','lombardst','02/10/2021 14:57:00'),
(1619,1619,' ','Zia','Broadst','lombardst','02/11/2021 14:57:00'),
(1619,1619,'Ava','Zia','Broadst','lombardst','02/12/2021 13:57:00'),

Below in my select statement below its returning all the 4 rows but if you look at the
first two rows there is not change in actual values (id,name and street) so i want to select only one row with latest updated date.

So i want to display only below three records : First row should be eliminated as its not the latesttime.
(1619,1619,'Eva','Zia','Broadst','lombardst','02/10/2021 14:57:00'),
(1619,1619,' ','Zia','Broadst','lombardst','02/11/2021 14:57:00'),
(1619,1619,'Ava','Zia','Broadst','lombardst','02/12/2021 13:57:00'),

Select distinct * from TableK
Where oldid<>newid or oldname<>newname or oldstreet<>newstreet

How do i write my select query ? Thanks

Answer 1

Hi @kkran ，

According to your requirement, you could try with max(lastupdatedt ) ... group by... as below:

Select oldID,newid,oldname,newname,oldstreet,newstreet  
,max(lastupdatedt) lastupdatedt   
from TableK  
group by oldID,newid,oldname,newname,oldstreet,newstreet  

Output :

oldID	newid	oldname	newname	oldstreet	newstreet	lastupdatedt  
1619	1619	 	Zia	Broadst	lombardst	2021-02-11 14:57:00.000  
1619	1619	Ava	Zia	Broadst	lombardst	2021-02-12 13:57:00.000  
1619	1619	Eva	Zia	Broadst	lombardst	2021-02-10 14:57:00.000  

If above query is not working, please provide more sample data and expected output.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.