SQL query to fetch top record if the desired column value is duplicated

Question

In below Table the Primary Key is: Id, ClientId, CertificateId. The column AccountId is duplicated because AccountId may have different client and clients may have different certificates. Below script for table and data to manipulate.

SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[TableB](
        [Id] [int] NOT NULL,
        [ClientId] [int] NOT NULL,
        [CertificateId] [int] NOT NULL,
        [AccountId] [int] NOT NULL,
        [Status] [bit] NULL,
     CONSTRAINT [PK_TableB] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC,
        [ClientId] ASC,
        [CertificateId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[TableB] ([Id], [ClientId], [CertificateId], [AccountId], [Status]) VALUES (1, 5, 34, 1, 1)
    INSERT [dbo].[TableB] ([Id], [ClientId], [CertificateId], [AccountId], [Status]) VALUES (2, 8, 34, 1, 1)
    INSERT [dbo].[TableB] ([Id], [ClientId], [CertificateId], [AccountId], [Status]) VALUES (3, 7, 36, 2, 1)
    INSERT [dbo].[TableB] ([Id], [ClientId], [CertificateId], [AccountId], [Status]) VALUES (4, 9, 37, 3, 1)
    INSERT [dbo].[TableB] ([Id], [ClientId], [CertificateId], [AccountId], [Status]) VALUES (5, 10, 35, 4, 1)
    INSERT [dbo].[TableB] ([Id], [ClientId], [CertificateId], [AccountId], [Status]) VALUES (6, 4, 37, 4, 0)
    INSERT [dbo].[TableB] ([Id], [ClientId], [CertificateId], [AccountId], [Status]) VALUES (7, 61, 34, 4, 1)
    INSERT [dbo].[TableB] ([Id], [ClientId], [CertificateId], [AccountId], [Status]) VALUES (8, 45, 35, 5, 1)

    GO

When I execute this query: SELECT * FROM TableB WHERE [Status]=1 I get the following results

Id  |  ClientId |  CertificateId  | AccountId | Status
1   |   5       |      34         |     1     |   1
2   |   8       |      34         |     1     |   1
3   |   7       |      36         |     2     |   1
4   |   9       |      37         |     3     |   1
5   |   10      |      35         |     4     |   1
7   |   61      |      34         |     4     |   1
8   |   45      |      35         |     5     |   1

Please help me to get below result, means if any AccountId is repeated then select top 1 and output should like this

Id  |  ClientId |  CertificateId  | AccountId | Status
1   |   5       |      34         |     1     |   1
3   |   7       |      36         |     2     |   1
4   |   9       |      37         |     3     |   1
5   |   10      |      35         |     4     |   1
8   |   45      |      35         |     5     |   1

`select * from (select *, RN = row_number() over (partition by AccountId order by Id) from TableB where [Status] = 1) x where RN = 1` — S3S, Dec 20 '18 at 21:40

Derviş Kayımbaşıoğlu · Answer 1 · 2018-12-20T21:46:35.137

1

Take a look at below query. Please note that I used Partitioning in order to get grouped row_numbers (rn) , then filtered query by using rn

WITH t AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY AccountId ORDER BY Id Asc) AS rn
   FROM TableB WHERE [Status]=1
)
SELECT *
FROM t
WHERE rn = 1

edited Dec 20 '18 at 21:46

answered Dec 20 '18 at 21:41

Derviş Kayımbaşıoğlu

28,492
4
50
72

2

Shouldn't it be `ORDER BY Id ASC`? But again, this is a duplicate question. – The Impaler Dec 20 '18 at 21:42
Yes, good catch :) – Derviş Kayımbaşıoğlu Dec 20 '18 at 21:42
Tagging on to @TheImpaler, you will also need a `WHERE` clause in that CTE to account for the `STATUS`, otherwise a `STATUS != 1` could result in `rn = 1` – S3S Dec 20 '18 at 21:45

Gordon Linoff · Answer 2 · 2018-12-20T23:11:21.617

1

Often the fastest method uses a correlated subquery:

SELECT b.*
FROM TableB b
WHERE b.id = (SELECT MIN(b2.id)
              FROM tableB b2
              WHERE b2.Status = 1 AND
                    b2.AccountId = b.AccountId
             );

You want an index on tableB(AccountId, status, id) for performance.

edited Dec 20 '18 at 23:11

answered Dec 20 '18 at 21:51

Gordon Linoff

1,242,037
58
646
786

1

shouldn't `max` be `min` and the `where` clause be in the subquery as well? – S3S Dec 20 '18 at 22:09
Is this query perform faster than above CTE query,when there are million records – user3803537 Dec 20 '18 at 22:13
1

@scsimon . . . The `max()` should be `min()` and the filtering on `status` only needs to be in the subquery, not the outer query. – Gordon Linoff Dec 20 '18 at 23:11
@user3803537 . . . The query with a CTE uses `ROW_NUMBER()`. I think this version will be faster with the right indexing. – Gordon Linoff Dec 20 '18 at 23:12
Good point. Only needs to be inner query – S3S Dec 21 '18 at 02:25

score 0 · Answer 3 · answered Dec 20 '18 at 21:42

0

Looks like you want one record per account and then the minimum values for the other fields. If so, just group by account like so:

SELECT
Min(Id) Id,
Min(ClientId) ClientId,
Min(CertificateId) CertificateId,
AccountId,
Status
FROM TableB
WHERE Status=1
GROUP BY AccountId, Status

answered Dec 20 '18 at 21:42

Paul X

379
1
3
10

This assumes the ClientId and CertificateId would always increment which, as you can see, neither does – S3S Dec 20 '18 at 21:44
No it doesn't. Even if ClientID duplicate, the query would just grab the Min. Seems like exactly what the question asks for using basic sql (Partitioning would work as well, but that is more advanced sql) – Paul X Dec 20 '18 at 21:50
But the `MIN` may not be the first instance i.e. top record... thus `MIN` would produce incorrect results. To elaborate, your Id, ClientID and CertificateId could all come from different rows... and that is NOT what the OP wants. – S3S Dec 20 '18 at 22:03

SQL query to fetch top record if the desired column value is duplicated

3 Answers3