Using DISTINCT, GROUP BY, and ROW_NUMBER for De‑duplication in SQL

When extracting data with SQL, duplicate records often appear, such as when calculating unique visitors (UV), requiring de‑duplication.

In MySQL the typical approaches are DISTINCT or GROUP BY , while databases that support window functions (e.g., Hive, Oracle) can also use ROW_NUMBER() for more flexible de‑duplication.

Consider a table Task with columns task_id (task identifier), order_id (order identifier) and start_time . One task may correspond to multiple orders, so task_id is not unique.

To obtain the total number of distinct tasks, the following methods can be used:

DISTINCT

-- list all unique task_id values
-- select distinct task_id from Task;

-- total number of tasks
select count(distinct task_id) as task_num
from Task;

The DISTINCT clause is simple but can be less efficient and cannot be used to display the distinct rows together with other columns without additional handling.

GROUP BY

-- list unique task_id values (null is also a value)
-- select task_id from Task group by task_id;

-- total number of tasks
select count(task_id) as task_num
from (
    select task_id
    from Task
    group by task_id
) tmp;

ROW_NUMBER() (window function)

-- use in databases that support window functions
select count(case when rn = 1 then task_id else null end) as task_num
from (
    select task_id,
           row_number() over (partition by task_id order by start_time) as rn
    from Task
) tmp;

An additional example with a helper table Test demonstrates how DISTINCT and GROUP BY behave differently when multiple columns are selected.

select distinct user_id from Test;                     -- returns 1, 2
select distinct user_id, user_type from Test;          -- returns 1 1, 1 2, 2 1
select user_id from Test group by user_id;             -- returns 1, 2
select user_id, user_type from Test group by user_id, user_type; -- returns 1 1, 1 2, 2 1

These snippets illustrate the syntax and performance considerations of each de‑duplication technique.