CODEWITHSUNDEEP
t-sql
Bhushan
Bhushan

Reputation: 134

Selecting Duplicate

I want to retrieve only Duplicated records not unique records. Suppose I have data which consists of as below Ids 1,2,1 Names A,B,A, Quantity 10,15,20 I want like Sno 1 Id 1,Name A quanity 10, Sno 2 Id 1 Name A,quanitity 20

Please Suggest.

Thanks Sasi

Upvotes: 0

Views: 29

Answers (3)

MEdwin
MEdwin

Reputation: 2960

like your request, you need to create a new column [SNo] that is partitioned on the orignal columns (Names, Id). Those with [SNo] >1 are duplicates. To Filter, just get Duplicates>1. See a mockup below:

enter image description here

DECLARE @Records TABLE (Id int, Names VARCHAR(10), [Quantity] INT)
INSERT INTO @Records
SELECT 1, 'A',10 UNION ALL
SELECT 2, 'B',15 UNION ALL
SELECT 1, 'A',20



----To Get Duplicates -----

    SELECT SNo, Id, Names, Quantity

    FROM
    (
    SELECT 
        SNo=ROW_NUMBER()over(PARTITION BY Names,Id order by Id),
        Duplicates=COUNT(*) OVER (PARTITION BY [ID], Names),
        *
    FROM
        @Records
    )M
    WHERE 
        Duplicates>1

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37480

More general solution:

SELECT t1.Id, t1.Name, t1.Quantity
FROM MyTable t1 JOIN (
    SELECT Id, Name
    FROM MyTable
    GROUP BY Id, Name
    HAVING COUNT(*) > 1
) t2 ON t1.Id = t2.Id AND t1.Name = t2.Name

Upvotes: 0

gotqn
gotqn

Reputation: 43666

Please, try this and let me know if something is not clear - do not ask separate question if you are not able to understand the code below:

DECLARE @DataSource TABLE
(
    [ID] INT
   ,[name] CHAR(1)
   ,[Quantity] INT 
);

INSERT INTO @DataSource ([ID], [name], [Quantity])
VALUES (1, 'A', '10')
      ,(2, 'B', '15')
      ,(1, 'A', '20');

WITH DataSource AS
(
    SELECT *
          ,COUNT(*) OVER (PARTITION BY [ID], [name]) AS [Count]
    FROM @DataSource
)
SELECT [ID]
      ,[name]
      ,[Quantity]
FROM Datasource
WHERE [Count] > 1;

enter image description here

The first part of the statement uses CTE. It allows as to compute something in advanced and then use it. In our case, this is the following line:

COUNT(*) OVER (PARTITION BY [ID], [name]) AS [Count]

We are using the OVER clause of COUNT function specifying in its PARTITION BY clause which columns to use fro grouping. If you run only the above line of code you will get this:

SELECT *
      ,COUNT(*) OVER (PARTITION BY [ID], [name]) AS [Count]
FROM @DataSource;

enter image description here

You can see that the Count column is showing how many rows are containing the unique pair of ID and name values. The window functions let us to performing aggregations using different groupings and getting the value for each row.

Now, what is left is to query the this data from the common table expression and show only rows which have Count greater then 1.

Upvotes: 1

Related Questions