How to Restrict following Records in sql server

Posted by Jayakumars under Sql Server on 1/13/2017 | Points: 10 | Views : 989 | Status : [Member] [MVP] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

hi
How to Restrict duplicate latest datetime records only show in this code

create table enamelist
(
id int primary key identity(1,1),
EmpId int,
EmpName varchar(100),
EmpCreatedDate datetime
)


select * from enamelist

insert into enamelist values(1,'John',getdate())
insert into enamelist values(1,'John',getdate())
insert into enamelist values(2,'Gilbert David',getdate())
insert into enamelist values(2,'Gilbert David',getdate())
insert into enamelist values(3,'Rufees David',getdate())
insert into enamelist values(3,'Rufees David',getdate())
insert into enamelist values(4,'Denish David',getdate())
insert into enamelist values(5,'ReDenish David',getdate())


SELECT ID,EMPID,EMPNAME,EMPCREATEDDATE FROM ENAMELIST
GROUP BY ID,EMPID,EMPNAME,EMPCREATEDDATE

-- My Data Like this. but i put group also but Duplicate records shows here because Datetime Different so
-- that record comes. But i need every Duplicate records i need latest EMPCREATEDDATE only show
-- When Duplicates Records i need shows latest dateand time only show

-- I need output this in Sql Query

Sno EMPID name createddate
1 1 John 2017-01-13 11:23:02.370
2 2 Gilbert John 2017-01-13 11:23:02.380
3 3 Rufees John 2017-01-13 11:23:02.427
4 4 Denish David 2017-01-13 11:26:24.327
5 5 ReDenish David 2017-01-13 11:26:24.357

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: A2H on: 1/13/2017 [Member] [MVP] Silver | Points: 25

0	You can try with the below query select * from ( select EMPID,EMPNAME,EMPCREATEDDATE, row_number() over(partition by EMPNAME order by EMPCREATEDDATE desc) as rn from ENAMELIST ) t where t.rn = 1 ORDER BY t.EMPID asc Thanks, A2H My Blog Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 1/19/2017 [Member] [MVP] Platinum | Points: 25

0	SELECT EMPID,EMPNAME,MAX(EMPCREATEDDATE) FROM ENAMELIST GROUP BY EMPID,EMPNAME Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts