SQL Query to pick up IDs that are related to another Group ID when some are set to a certain status

Question

This is a bit of a complex description for a title but I will try to explain it better here. I have the below data.

+---------+----------+--------+
| GroupID | UniqueID | Status |
+---------+----------+--------+
|       1 |        1 |      3 |
|       1 |        2 |      3 |
|       1 |        3 |      2 |
|       2 |        4 |      3 |
|       2 |        5 |      3 |
|       3 |        6 |      1 |
|       3 |        7 |      1 |
+---------+----------+--------+

Group ID is a shared key to link rows together. Unique ID is a completely unique key. Status is a value field.

I have been thinking about this query for a while but I just can't get my head around what I can do to get my desired results.

I want to return all the unique ids for the orders that have a Status of 3. In addition to that, these orders must have at least one order that they are linked to via GroupID set to 1 or 2.

So for GroupID "1", we have 3 records: The first line has status 3 and another order in the same group set to 1 or 2. (include in results). The second line has status and another order in the same group set to 1 or 2. (include in results). The third line has status 2 so not included in results.

So for GroupID "2", we have 2 records: Both records have status "3" but there is no record with status "1" or "2" therefore they are not shown in the results.

So continuing on with that logic, for the example data the output should be:

+---------+----------+--------+
| GroupID | UniqueID | Status |
+---------+----------+--------+
|       1 |        1 |      3 |
|       1 |        2 |      3 |
+---------+----------+--------+

Let me know if I need to clarify further.

Answer 1

I understand that you want rows in status 3 for which another row exists with the same group and another status. One option uses exists:

select t.*
from mytable t
where status = 3 and exists (
    select 1
    from mytable t1
    where t1.groupid = t.groupid and t1.status <> t.status
)

You could also use window functions:

select groupid, uniqueid, status
from (
    select t.*,
        min(status) over(partition by groupid) min_status,
        max(status) over(partition by groupid) max_status
    from mytable t
) t
where status = 3 and min_status <> max_status