Deleting Duplicate Records - Single Query

I had a requirement where I wanted to delete all the duplicate email ids from my table.

My table structure is :

* EMAIL_ID uniqueidentifier PRIMARY KEY
* EMAIL varchar (128) Stores the email address


I write the following query to find out the duplicate records :

SELECT EMAIL, COUNT(*) DUPLICATES
FROM EMAILS
GROUP BY EMAIL
HAVING COUNT(*) > 1
ORDER BY DUPLICATES


Here's the delete Query:

Here's the delete Query:

DELETE FROM EMAILS
WHERE EMAIL_ID IN
(
SELECT EMAIL_ID FROM
(
SELECT EMAIL, EMAIL_ID
, RANK() OVER (PARTITION BY EMAIL ORDER BY EMAIL_ID) AS EMAIL_RANK
FROM EMAILS
WHERE EMAIL IN
(
SELECT EMAIL
FROM EMAILS
GROUP BY EMAIL
HAVING COUNT(*) > 1
)
) A
WHERE A.EMAIL_RANK != 1
)

There were lot of other approaches which I found, most of them suggested using cursors or using Set Row_Count 1 and then running the delete query iteratively. But I wanted to do it in one shot.

It took me less than a second to delete almost 1000 duplicate rows from around 10,000 rows in my table.

I am sure there would be better approaches, surely to avoid multiple IN clauses. Just in case you have one put in the comments.

I got to figure out one for Oracle as well.

How to find the N th highest row in a table ?

One of most common requirements in a SQL query is to find the 2nd ..3rd ..Nth highest Value in a particular column of a table.

Common Interview Question as well.

There are various ways to do find the second highest.
eg:
1. select MAX(Column) from TABLE
where Column NOT IN
(select Max(Column) from TABLE)


2. select MAX(Column) from TABLE
where Column <
(select Max(Column) from TABLE)

3. Using Minus in SQL Server

However, when we want to find the 3rd / 4th highest the query gets complicated and performance would take a hit.

A generic of doing the same would be:


SELECT
FROM (
select ROW_NUMBER() OVER (ORDER BY Column DESC) A,
from table
) TAB
WHERE A = N

(where N is the required value)

If you know more efficient ways then please put in a comment.