I have a table of aliases linked to values. I need to find duplicate aliases with unique record ids.
To explain better, here’s the table:
<code>ID Alias Value
---------------------
1 000123 3
2 000123 3
3 000234 4
4 000123 3
5 000345 5
6 000345 5
</code>
<code>ID Alias Value
---------------------
1 000123 3
2 000123 3
3 000234 4
4 000123 3
5 000345 5
6 000345 5
</code>
ID Alias Value
---------------------
1 000123 3
2 000123 3
3 000234 4
4 000123 3
5 000345 5
6 000345 5
The result of a query on this table should be something to the effect of
<code>ID Alias Value1 Value2
---------------------------------------
1 000123 3 3
2 000123 3
3 000234 4 4
4 000123 3
5 000345 5 5
6 000345 5
</code>
<code>ID Alias Value1 Value2
---------------------------------------
1 000123 3 3
2 000123 3
3 000234 4 4
4 000123 3
5 000345 5 5
6 000345 5
</code>
ID Alias Value1 Value2
---------------------------------------
1 000123 3 3
2 000123 3
3 000234 4 4
4 000123 3
5 000345 5 5
6 000345 5
I am currently using EXCEL to do the workaround:
<code>=IF(COUNTIF($F$2:$F109889, $F109889)=1, $I109889, "")
</code>
<code>=IF(COUNTIF($F$2:$F109889, $F109889)=1, $I109889, "")
</code>
=IF(COUNTIF($F$2:$F109889, $F109889)=1, $I109889, "")
<code>Alias -- Value1 Value2
---------------------------------------
16986 1231 2639.26 2639.26
16986 1231 2639.26
16986 1231 2639.26
16986 1231 2639.26
16986 1231 2639.26
16986 1231 2639.26
16986 1231 2639.26
16986 1231 2639.26
16986 1231 2639.26
16986 1231 2639.26
</code>
<code>Alias -- Value1 Value2
---------------------------------------
16986 1231 2639.26 2639.26
16986 1231 2639.26
16986 1231 2639.26
16986 1231 2639.26
16986 1231 2639.26
16986 1231 2639.26
16986 1231 2639.26
16986 1231 2639.26
16986 1231 2639.26
16986 1231 2639.26
</code>
Alias -- Value1 Value2
---------------------------------------
16986 1231 2639.26 2639.26
16986 1231 2639.26
16986 1231 2639.26
16986 1231 2639.26
16986 1231 2639.26
16986 1231 2639.26
16986 1231 2639.26
16986 1231 2639.26
16986 1231 2639.26
16986 1231 2639.26
But due to the # of records (around 200k rows), sometimes my laptop will hang for over an hour.
Are there any solutions out there that would work better?
I’m trying to do the report on SQL Server instead of in Excel.
New contributor
Grayson Chee is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
<code>SELECT
ID
,Alias
,Value1 = Value
,Value2 = IIF(COUNT(*)OVER(PARTITION BY Alias
ORDER BY ID
ROWS UNBOUNDED PRECEDING) = 1
,Value
,NULL )
,Value2_2 = IIF(COUNT(*)OVER(PARTITION BY Alias
ORDER BY ID
ROWS UNBOUNDED PRECEDING) = 1
,FORMAT(Value,'0')
,'' )
FROM (VALUES
(1, '000123', 3)
,(2, '000123', 3)
,(3, '000234', 4)
,(4, '000123', 3)
,(5, '000345', 5)
,(6, '000345', 5)
)v(ID, Alias, Value)
ORDER BY ID
</code>
<code>SELECT
ID
,Alias
,Value1 = Value
,Value2 = IIF(COUNT(*)OVER(PARTITION BY Alias
ORDER BY ID
ROWS UNBOUNDED PRECEDING) = 1
,Value
,NULL )
,Value2_2 = IIF(COUNT(*)OVER(PARTITION BY Alias
ORDER BY ID
ROWS UNBOUNDED PRECEDING) = 1
,FORMAT(Value,'0')
,'' )
FROM (VALUES
(1, '000123', 3)
,(2, '000123', 3)
,(3, '000234', 4)
,(4, '000123', 3)
,(5, '000345', 5)
,(6, '000345', 5)
)v(ID, Alias, Value)
ORDER BY ID
</code>
SELECT
ID
,Alias
,Value1 = Value
,Value2 = IIF(COUNT(*)OVER(PARTITION BY Alias
ORDER BY ID
ROWS UNBOUNDED PRECEDING) = 1
,Value
,NULL )
,Value2_2 = IIF(COUNT(*)OVER(PARTITION BY Alias
ORDER BY ID
ROWS UNBOUNDED PRECEDING) = 1
,FORMAT(Value,'0')
,'' )
FROM (VALUES
(1, '000123', 3)
,(2, '000123', 3)
,(3, '000234', 4)
,(4, '000123', 3)
,(5, '000345', 5)
,(6, '000345', 5)
)v(ID, Alias, Value)
ORDER BY ID
2