This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(259.20000000000005, 56.00000000000001%, 35%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EEO%3C/text%3E%3C/svg%3E)
hello,
I have a table that has two column that contain similar data, but I want to check the two column col1 and col2, if the number of data in col1 is equal to number of data col2, make a temp list of col1 data that has lesser number of data in col2.
my table is like this.
col1 || col2
0001 || 0001
0002 || 0001
0003 || 0002
0004 || 0002
0005 || 0003
0006 || 0004
0007 || 0005
so I want to check col1 against col2, any data in col2 that is less than 2 will be listed on a temp list, from the temp list I will like to select any number of row.
Please anyone with Idea on how I can solve this.
Thanks.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(236.8, 76%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EEM%3C/text%3E%3C/svg%3E)
Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding!
Please also try:
;WITH cte as(SELECT DISTINCT col2,COUNT(col2) OVER(PARTITION BY col2) cc
FROM #test)
SELECT t.col1,t.col2,
CASE WHEN cc>=2 THEN NULL ELSE t.col1 END temp
FROM #test t
LEFT JOIN cte c ON t.col1=c.col2
Regards
Echo
Thank you very much Echo, I tried your solution it gave what I expected, but one issue left.
I would like to select one row out of the returned temp column, how do I add my query to be able to make that select?
Do you just want to return one row? If yes, please try:
;WITH cte
as(SELECT DISTINCT col2,COUNT(col2) OVER(PARTITION BY col2) cc
FROM #test)
SELECT top(1) t.col1,t.col2,
CASE WHEN cc>=2 THEN NULL ELSE t.col1 END temp
FROM #test t
LEFT JOIN cte c ON t.col1=c.col2
ORDER BY t.col1
Echo
Yes, I am using C#, I want to select one row of the returned temp column.
Example, if it return list of data that are less than 2 in col2, I will like to select one from the list and use it in C# code.
I don't know much about C#, but the above code can return a row in temp that meets the condition. If you have questions about C#, you can go to the C# forum to ask questions, and the people there will provide you with more professional help.
Regards
Echo
For your additional explanation, try this query:
select col1, col2,
case when (select count(*) from #test where col2 = t.col1) < 2 then col1 end as temp
from #test t