Wednesday, May 30, 2018

Alternate of Group BY || Duplicate remove without group by


CREATE TABLE [dbo].[tempEmp](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [deptid] [int] NULL,
 [name] [varchar](50) NULL
) ON [PRIMARY]

 
INSERT [dbo].[tempEmp] ([id], [deptid], [name]) VALUES (1, 501, N'test1')
GO
INSERT [dbo].[tempEmp] ([id], [deptid], [name]) VALUES (2, 502, N'test2')
GO
INSERT [dbo].[tempEmp] ([id], [deptid], [name]) VALUES (3, 501, N'test11')
GO
INSERT [dbo].[tempEmp] ([id], [deptid], [name]) VALUES (4, 503, N'test3')
GO
INSERT [dbo].[tempEmp] ([id], [deptid], [name]) VALUES (5, 503, N'test33')
GO
INSERT [dbo].[tempEmp] ([id], [deptid], [name]) VALUES (6, 503, N'test333')
GO
INSERT [dbo].[tempEmp] ([id], [deptid], [name]) VALUES (7, 504, N'test4')
GO
INSERT [dbo].[tempEmp] ([id], [deptid], [name]) VALUES (8, 505, N'test5')
GO
INSERT [dbo].[tempEmp] ([id], [deptid], [name]) VALUES (9, 505, N'test55')
 
 select * from tempEmp where id in(
select max(id) from tempEmp group by deptid
)

select * from tempEmp A where id in(
 select max(id) from tempEmp B where A.deptid=B.deptid
)

No comments:

Post a Comment

javascript Filter/index off

 var family = [{"name":"Jack",  "age": 26},               {"name":"Jill",  "age"...