Remove duplicated rows in sql query

Question

I have a table with the following structure

[id] [int] IDENTITY(1,1) NOT NULL,
[account_number] [int] NOT NULL,
[account_name] [varchar(100)] NULL,
[account_chapter] [varchar(20)] NULL,

There can be many rows with the same account_number, but differents account_name and account_chapters.

For example, we can have something like the following :

id  account_number  account_name  account_chapter
12  1111              Name01        chapter01
13  1111              Name02        chapter02
14  2222              Name03        chapter07
15  2222              Name05        chapter11
16  7777              Name06        chapter44

What i want is a query that for each account_number, filter only the first occurence in the table. For example, the query above must be transformed in the following :

id  account_number  account_name  account_chapter
12  1111              Name01        chapter01
14  2222              Name03        chapter07
16  7777              Name06        chapter44

Here is the query i wrote :

with req01 as (select distinct account_number from accounts)
select * from req01 full join (select  * from accounts) as p on p.account_number = req01.account_number

It do not produce the expected result.

Any help ? Thanks.

If your `id` is an identity, why is it repeated? – Gordon Linoff Sep 11 '21 at 12:04 — Gordon Linoff, Sep 11 '21 at 12:04

score 3 · Accepted Answer · answered Sep 11 '21 at 09:16

Use ROW_NUMBER:

SELECT TOP 1 WITH TIES *
FROM accounts
ORDER BY ROW_NUMBER() OVER (PARTITION BY account_number ORDER BY account_chapter);

Or, using ROW_NUMBER in a more typical way:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY account_number
                                 ORDER BY account_chapter) rn
    FROM accounts
)

SELECT id, account_number, account_name, account_chapter
FROM cte
WHERE rn = 1;

Note that both of these answers assumes that the account_chapter version determines which of the "duplicates" is actually first.

score -2 · Answer 2 · answered Sep 11 '21 at 12:06

Normally, the "first" occurrence in the table would be determined by a date/time or identity column. You have only the identity, so you seem to want:

select a.*
from accounts a
where a.id = (select min(a2.id)
              from accounts a2
              where a2.account_number = a.account_number
             );

Remove duplicated rows in sql query

2 Answers2