CODEWITHSUNDEEP
sqlsql-serverdistinct
bagusofterror
bagusofterror

Reputation: 75

How to Distinct query result over 2 of 3 columns

I have a query like this:

WITH CTE_KELOMPOKINFORMASI (KelompokInformasi, XBRLItem_ItemId) 
AS (
SELECT a.Id AS KelompokInformasi, c.XBRLItem_ItemId
FROM XBRLNamespaces a INNER JOIN XBRLHypercubes b 
ON a.XBRLView_ViewId = b.XBRLView_ViewId 
INNER JOIN XBRLHypercubeDimensionItems c 
ON b.XBRLHypercubeId = c.XBRLHypercube_XBRLHypercubeId 
WHERE a.Id like '%KBIK_AAKL%')

SELECT f.KelompokInformasi, e.Name AS DimensionName, 
ROW_NUMBER() OVER(PARTITION BY e.Name ORDER BY e.Name DESC) AS 'Number'
FROM XBRLDefinitionRoleDomainItems a 
LEFT OUTER JOIN XBRLDefinitionRoleDimensionItems b
ON a.XBRLDefinitionRole_DefinitionRoleId = b.XBRLDefinitionRole_DefinitionRoleId 
LEFT OUTER JOIN XBRLItems c ON a.XBRLItem_ItemId = c.ItemId 
LEFT OUTER JOIN XBRLLabels d ON a.XBRLItem_ItemId = d.XBRLItem_ItemId 
LEFT OUTER JOIN XBRLItems e ON b.XBRLItem_ItemId=e.ItemId
LEFT OUTER JOIN CTE_KELOMPOKINFORMASI f ON b.XBRLItem_ItemId=f.XBRLItem_ItemId
WHERE b.XBRLItem_ItemId=f.XBRLItem_ItemId

and it gives result like this:

Kelompok Informasi     DimensionName       Number
KBIK_AAKL              JWAAKT              1
KBIK_AAKL              JWAAKT              2
KBIK_AAKL              JWAAKT              3
KBIK_AAKL              SNOUPL              1
KBIK_AAKL              SNOUPL              2
KBIK_AAKL              KOKOLT              1
KBIK_AAKL              KOKOLT              2
KBIK_AAKL              KOKOLT              3
KBIK_AAKL              KOKOLT              4

Is it possible if i DISTINCT my result again by "DimensionName", so i have a result just like this:

KelompokInformasi      DimensionName       Number
KBIK_AAKL              JWAAKT              1
KBIK_AAKL              SNOUPL              2
KBIK_AAKL              KOKOLT              3

i tried to do distinct like code below but it doesn't work

SELECT DISTINCT DimensionName
FROM (SELECT f.KelompokInformasi, e.Name AS DimensionName, 
ROW_NUMBER() OVER(PARTITION BY e.Name ORDER BY e.Name DESC) AS 'Number'
FROM XBRLDefinitionRoleDomainItems a 
LEFT OUTER JOIN XBRLDefinitionRoleDimensionItems b
ON a.XBRLDefinitionRole_DefinitionRoleId = b.XBRLDefinitionRole_DefinitionRoleId 
LEFT OUTER JOIN XBRLItems c ON a.XBRLItem_ItemId = c.ItemId 
LEFT OUTER JOIN XBRLLabels d ON a.XBRLItem_ItemId = d.XBRLItem_ItemId 
LEFT OUTER JOIN XBRLItems e ON b.XBRLItem_ItemId=e.ItemId
LEFT OUTER JOIN CTE_KELOMPOKINFORMASI f ON b.XBRLItem_ItemId=f.XBRLItem_ItemId
WHERE b.XBRLItem_ItemId=f.XBRLItem_ItemId)

Upvotes: 1

Views: 83

Answers (2)

wruckie
wruckie

Reputation: 1803

Use your distinct on the inner subquery and row number them on the outer Select

WITH CTE_KELOMPOKINFORMASI (KelompokInformasi, XBRLItem_ItemId) 
AS (
SELECT a.Id AS KelompokInformasi, c.XBRLItem_ItemId
FROM XBRLNamespaces a INNER JOIN XBRLHypercubes b 
ON a.XBRLView_ViewId = b.XBRLView_ViewId 
INNER JOIN XBRLHypercubeDimensionItems c 
ON b.XBRLHypercubeId = c.XBRLHypercube_XBRLHypercubeId 
WHERE a.Id like '%KBIK_AAKL%')

Select A.*, ROW_NUMBER() OVER(ORDER BY A.DimensionName DESC) AS 'Number'
FROM (
    SELECT distinct f.KelompokInformasi, e.Name AS DimensionName
    FROM XBRLDefinitionRoleDomainItems a 
    LEFT OUTER JOIN XBRLDefinitionRoleDimensionItems b
    ON a.XBRLDefinitionRole_DefinitionRoleId = b.XBRLDefinitionRole_DefinitionRoleId 
    LEFT OUTER JOIN XBRLItems c ON a.XBRLItem_ItemId = c.ItemId 
    LEFT OUTER JOIN XBRLLabels d ON a.XBRLItem_ItemId = d.XBRLItem_ItemId 
    LEFT OUTER JOIN XBRLItems e ON b.XBRLItem_ItemId=e.ItemId
    LEFT OUTER JOIN CTE_KELOMPOKINFORMASI f ON b.XBRLItem_ItemId=f.XBRLItem_ItemId
    WHERE b.XBRLItem_ItemId=f.XBRLItem_ItemId
) AS A

Upvotes: 2

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

Just Filter with Your Row_Number

Try like this

SELCT KelompokInformasi,DimensionName,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS 'NUMBER' FROM
(
SELECT f.KelompokInformasi, e.Name AS DimensionName, 
ROW_NUMBER() OVER(PARTITION BY e.Name ORDER BY e.Name DESC) AS 'RN'    
FROM XBRLDefinitionRoleDomainItems a 
LEFT OUTER JOIN XBRLDefinitionRoleDimensionItems b
ON a.XBRLDefinitionRole_DefinitionRoleId = b.XBRLDefinitionRole_DefinitionRoleId 
LEFT OUTER JOIN XBRLItems c ON a.XBRLItem_ItemId = c.ItemId 
LEFT OUTER JOIN XBRLLabels d ON a.XBRLItem_ItemId = d.XBRLItem_ItemId 
LEFT OUTER JOIN XBRLItems e ON b.XBRLItem_ItemId=e.ItemId
LEFT OUTER JOIN CTE_KELOMPOKINFORMASI f ON b.XBRLItem_ItemId=f.XBRLItem_ItemId
WHERE b.XBRLItem_ItemId=f.XBRLItem_ItemId

) AS T

WHERE RN = 1

(OR)

SELCT KelompokInformasi,DimensionName,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS 'NUMBER' FROM
    (
    SELECT f.KelompokInformasi, e.Name AS DimensionName,          
    FROM XBRLDefinitionRoleDomainItems a 
    LEFT OUTER JOIN XBRLDefinitionRoleDimensionItems b
    ON a.XBRLDefinitionRole_DefinitionRoleId = b.XBRLDefinitionRole_DefinitionRoleId 
    LEFT OUTER JOIN XBRLItems c ON a.XBRLItem_ItemId = c.ItemId 
    LEFT OUTER JOIN XBRLLabels d ON a.XBRLItem_ItemId = d.XBRLItem_ItemId 
    LEFT OUTER JOIN XBRLItems e ON b.XBRLItem_ItemId=e.ItemId
    LEFT OUTER JOIN CTE_KELOMPOKINFORMASI f ON b.XBRLItem_ItemId=f.XBRLItem_ItemId
    WHERE b.XBRLItem_ItemId=f.XBRLItem_ItemId
    GROUP BY f.KelompokInformasi, e.Name        
    ) AS T

Upvotes: 0

Related Questions