SQL Window Functions Setting up a flag if other rows have a common property
Example
Let's say I have this data:
Table items
| id | name | tag |
|---|---|---|
| 1 | example | unique_tag |
| 2 | foo | simple |
| 42 | bar | simple |
| 3 | baz | hello |
| 51 | quux | world |
I'd like to get all those lines and know if a tag is used by other lines
SELECT id, name, tag, COUNT(*) OVER (PARTITION BY tag) > 1 AS flag FROM items
The result will be:
| id | name | tag | flag |
|---|---|---|---|
| 1 | example | unique_tag | false |
| 2 | foo | simple | true |
| 42 | bar | simple | true |
| 3 | baz | hello | false |
| 51 | quux | world | false |
In case your database doesn't have OVER and PARTITION you can use this to produce the same result:
SELECT id, name, tag, (SELECT COUNT(tag) FROM items B WHERE tag = A.tag) > 1 AS flag FROM items A
