Multi-Row function to filter out Duplicates

Question

I'm relatively new at using SQL, So I would like your help regarding a case.

I have the following Table (just a sample):

| id | FName_LVL1  | LName_LVL1 | FName_LVL2 | LName_LVL2  |
|----|-------------|------------|------------|-------------|
| 1  | John        | Kennedy    | Marc       | Guy         |
| 2  | John        | Kennedy    | Olivier    | Oslo        |
| 3  | Mike        | Lanes      | Patrick    | James       |

I would like to isolate the duplicates in FName_LVL1 and LName_LVL1

So that the Table looks like this :

| id | FName_LVL1  | LName_LVL1 | FName_LVL2 | LName_LVL2  |
|----|-------------|------------|------------|-------------|
| 1  | John        | Kennedy    | Marc       | Guy         |
| 2  | John        | Kennedy    | Olivier    | Oslo        |

My idea was to create a flag column with a condition that IF lines Above or below in column FName_LVL1 and LName_LVL1 are the same, then put "1", else "0"

Having a Column looking like this:

| id | FName_LVL1  | LName_LVL1 | FName_LVL2 | LName_LVL2  | Flag
|----|-------------|------------|------------|-------------|
| 1  | John        | Kennedy    | Marc       | Guy         | 1
| 2  | John        | Kennedy    | Olivier    | Oslo        | 1
| 3  | Mike        | Lanes      | Patrick    | James       | 0

After having a table like this I could just filter and having the result I want to achieve.

That's a way to work I'm used to in Alteryx, but I'm not sure if this is possible using SQL statements, or even if this is the best way to tackle this case

Answer 1

You may use the count() with window function .

SQL Fiddle

Query 1:

SELECT t.*
    ,CASE 
        WHEN COUNT(*) OVER (
                PARTITION BY fname_lvl1
                ,lname_lvl1
                ) > 1
            THEN 1
        ELSE 0
        END AS Flag
FROM t

Results:

| ID | FNAME_LVL1 | LNAME_LVL1 | FNAME_LVL2 | LNAME_LVL2 | FLAG |
|----|------------|------------|------------|------------|------|
|  1 |       John |    Kennedy |       Marc |        Guy |    1 |
|  2 |       John |    Kennedy |    Olivier |       Oslo |    1 |
|  3 |       Mike |      Lanes |    Patrick |      James |    0 |