SQL Setting up a flag if other rows have a common property


Example

Let's say I have this data:

Table items

idnametag
1exampleunique_tag
2foosimple
42barsimple
3bazhello
51quuxworld

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:

idnametagflag
1exampleunique_tagfalse
2foosimpletrue
42barsimpletrue
3bazhellofalse
51quuxworldfalse

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