Setting up a flag if other rows have a common property

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