Eliminate duplicate rows

• rajaspdbjc

  SSC Rookie

  Points: 31

  More actions

  June 21, 2017 at 3:00 am

  #402200

  I need a query for the below scenario:
  AssetId    LabelId LabelName
  asset1    label1    abc
  asset2    label2    xyz
  asset2    label3    def
  asset3    label4    bcd
  asset3    label5    pqr

  Ideally the AssetId should be unique in case if we find a duplicate, we need to eliminate the duplicates
  The condition is explained as below:
  from the above sample data, lets assume that the table name is table1:
  case 1: if one asset is associated with one label then no change in the output, for example asset1 is having label1 which is unique
  case 2: if one asset is associated with more than one label then, find if labelname = xyz if found then get that row
  else if not found then get any label.

  the output is as below:

  AssetId LabelId LabelName
  asset1    label1    abc
  asset2    label2    xyz
  asset3    label4    bcd

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  June 21, 2017 at 3:16 am

  #1947508

  Try this.  It's not tested because you didn't supply DDL and INSERT statements.  What do you want to happen if there's more than one xyz?
  WITH Ordered AS (
    SELECT
         AssetId
    ,    LabelId
    ,    ROW_NUMBER() OVER (PARTITION BY AssetId ORDER BY
                            CASE
                                WHEN LabelName = 'xyz' THEN 0
                                ELSE 1
                            END
                            ) AS RowNo
    FROM table1
    )
SELECT
     AssetId
,    LabelId
,    LabelName
FROM Ordered
WHERE RowNo = 1

  John

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  June 21, 2017 at 3:34 am

  #1947509

  Slightly different flavor of John's fine solution
  
  USE TEEST;
GO
SET NOCOUNT ON;

  ;WITH SAMPLE_DATA(AssetId,LabelId,LabelName) AS
(
  SELECT
   X.AssetId
   ,X.LabelId
   ,X.LabelName
  FROM
  (VALUES
   ('asset1','label1','abc')
   ,('asset2','label2','xyz')
   ,('asset2','label3','def')
   ,('asset3','label4','bcd')
   ,('asset3','label5','pqr')
  ) X(AssetId,LabelId,LabelName)
)
,ORDERED_SET AS
(
  SELECT
   ROW_NUMBER() OVER
    (
      PARTITION BY SD.AssetId
      ORDER BY  ISNULL(NULLIF(SD.LabelName,'xyz'),CHAR(0))
    ) AS RID
   ,SD.AssetId
   ,SD.LabelId
   ,SD.LabelName
  FROM  SAMPLE_DATA  SD
)
SELECT
 OS.AssetId
,OS.LabelId
,OS.LabelName
FROM  ORDERED_SET  OS
WHERE OS.RID  = 1;
 

  Output
  

  AssetId LabelId LabelName
------- ------- ---------
asset1  label1  abc
asset2  label2  xyz
asset3  label4  bcd

  

• rajaspdbjc

  SSC Rookie

  Points: 31

  More actions

  June 21, 2017 at 3:47 am

  #1947511

  Thank you for your quick response..that resolved my issue.