Check if column has same value in any row

All we need is an easy explanation of the problem, so here it is.

I have the following case.

+-----------+--------+-------------+
| ArticleID | UserID | IsConnected |
+-----------+--------+-------------+
|         1 |     25 | True        |
|         1 |     45 | True        |
|         2 |     55 | False       |
+-----------+--------+-------------+

So I need to create a View who will return True if there is more than 1 record in the table with same ArticleID.

To make this easier the table looks the same, but without IsConnected column.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Something like this, may be

select 
 article_id, user_id, 
 case 
   when count(1) over (partition by article_id) > 1 then 'true' 
   else 'false' 
 end is_connected
from something

Method 2

As Charlieface mentioned, what you asked for could mean a few different things, so a little clarity would be good please. This will do literally exactly what you asked:

SELECT TOP 1 'True' AS SomeColumnName -- Returns a single row with 'True' when there's at least one pair of dupe rows by ArticleID, otherwise nothing is returned
FROM ArticlesTable
GROUP BY ArticleID
HAVING COUNT(1) > 1 -- Filter out all non-dupes

This makes it slightly better by returning an actual boolean-based field that evaluates to True or False, and always returns a value:

SELECT TOP 1 CAST(CASE WHEN COUNT(1) > 1 THEN 1 ELSE 0 END AS BIT) AS SomeColumnName -- Returns a single row with a boolean-based field with true or false dependent on if any ArticleID has dupes
FROM ArticlesTable
GROUP BY ArticleID
ORDER BY SomeColumnName DESC

Finally this will return all the grouped up rows by ArticleID with a boolean-based field for each one meaning if that ArticleID has dupes:

SELECT ArticleID, CAST(CASE WHEN COUNT(1) > 1 THEN 1 ELSE 0 END AS BIT) AS SomeColumnName -- Returns a boolean-based field describing if this particular ArticleID has dupes
FROM ArticlesTable
GROUP BY ArticleID
ORDER BY COUNT(1) DESC

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply