February 25, 2016 at 12:43 am
Hi Following is the result generated from query which gives rank
I need to set Code = xxxx for ID =ABC as this has different ranks
and for ID which has only 1 rank the code will be same as it is in the column CODE
Ranks ID CODE
1 ABC 1111
1 ABC 1111
2 ABC 234
3 ABC 123
1 XYZ 1111
1 XYZ 1111
1 XYZ 1111
1 XYZ 1111
final output should be like this
Ranks ID CODE
1 ABC XXXX
1 ABC XXXX
2 ABC XXXX
3 ABC XXXX
1 XYZ 1111
1 XYZ 1111
1 XYZ 1111
1 XYZ 1111
Thanks
February 25, 2016 at 1:33 am
Please post data in a consumable way. See http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ for guidelines.
How do you calculate the ranks? Please clarify.
-- Gianluca Sartori
February 25, 2016 at 1:49 am
sorry for the bad requirement given here is a detailed one and also the solution. please provide input if there is any better solution.
DECLARE @Temp table(ID varchar(100), code varchar(100))
INSERT INTO @Temp
SELECT 'ABC','1111'
UNION ALL
SELECT 'ABC','1111'
UNION ALL
SELECT 'ABC','234'
UNION ALL
SELECT 'ABC','123'
UNION ALL
SELECT 'XYZ','1111'
UNION ALL
SELECT 'XYZ','1111'
UNION ALL
SELECT 'XYZ','1111'
UNION ALL
SELECT 'XYZ','1111'
--SELECT * FROM @temp
;With rank_cte AS (
SELECT
DENSE_RANK() OVER(Partition By ID ORDER BY ID,code) as Ranks,
ID,
code
FROM @temp
)
SELECT
s.ID,IIF(t.ID = s.ID, 'XXXX',s.code)
FROM rank_cte AS s
CROSS APPLY (SELECT DISTINCT ID FROM rank_cte WHERE Ranks > 1) AS t
February 25, 2016 at 2:16 am
This seems to be slightly snappier:
SELECT Id, CASE WHEN MIN(Ranks) OVER(PARTITION BY ID) <> MAX(Ranks) OVER(PARTITION BY ID) THEN 'XXXX' ELSE Code END
FROM (
SELECT
DENSE_RANK() OVER(Partition By ID ORDER BY ID,code) as Ranks,
ID,
code
FROM @temp
) AS src
-- Gianluca Sartori
February 25, 2016 at 8:17 am
This also performs well. I thought it would perform better than Gianluca's, because it doesn't need the RANK, but the plan seems to think that they have the same cost. It will probably require a much larger dataset to do a comparison.
SELECT t.ID, CASE WHEN MAX(code) OVER(PARTITION BY ID) = MIN(code) OVER(PARTITION BY ID) THEN t.code ELSE 'XXXX' END
FROM @Temp t
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply