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

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

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


Got any SQL Question?