Write an SQL query to delete all the duplicate emails, keeping only one unique email with the smallest id. Note that you are supposed to write a DELETE statement and not a SELECT one.
table name: Person
Solution:
SELECT e.name from employee as e
left join employee as e1 on e.mangerId = e1.id
where e.salary > e1.salary
Output:
SQL Script:
CREATE TABLE [dbo].[employee](
[id] [int] NOT NULL,
[name] [varchar](50) NULL,
[salary] [int] NULL,
[mangerId] [int] NULL,
CONSTRAINT [PK_employee] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[employee] ([id], [name], [salary], [mangerId]) VALUES (1, N'Joe', 70000, 3)
GO
INSERT [dbo].[employee] ([id], [name], [salary], [mangerId]) VALUES (2, N'Henry', 80000, 4)
GO
INSERT [dbo].[employee] ([id], [name], [salary], [mangerId]) VALUES (3, N'Sam', 60000, NULL)
GO
INSERT [dbo].[employee] ([id], [name], [salary], [mangerId]) VALUES (4, N'Max', 90000, NULL)
GO