June 21, 2017 at 3:00 am
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
June 21, 2017 at 3:16 am
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
June 21, 2017 at 3:34 am
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
June 21, 2017 at 3:47 am
Thank you for your quick response..that resolved my issue.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply