How to Restrict following Records in sql server

Posted by Jayakumars under Sql Server on 1/13/2017 | Points: 10 | Views : 324 | Status : [Member] [MVP] | Replies : 2
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


Responses

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

Up
0
Down
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

Up
0
Down
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

Login to post response