check if rows has the same status-CodePudding

I would like to return the col1 that have different status using the ID as key in SQL SERVER.

Thanks

ID       col1      Status    
11       20        closed
11       90        closed
13       35        open
13       42        closed
20       55        open
20       70        open

CodePudding user response：

You can use COUNT(DISTINCT <expr>) as in:

select case count(distinct status) WHEN 0 then 'NO VALUES'
                                   WHEN 1 then 'EQUAL'
                                   ELSE 'DIFFERENT'
       end as comparison
from t
where id = 11

CodePudding user response：

You can just compare the min with the max and see if they are the same.

SELECT
  t.id,
  CASE WHEN MIN(t.status) = MAX(t.status)
    THEN 'Same'
    ELSE 'Diff' END AS comparison
FROM YourTable t
WHERE t.col1 IN (20, 90)
  AND t.ID = 11
GROUP BY
  t.id;

EDIT Now that you have completely changed the question, a different answer is needed.

You can use a conditional windowed count

SELECT
  t.*
FROM (
    SELECT t.*,
      CASE WHEN MIN(t.status) OVER (PARTITION BY t.id) <> MAX(t.status) OVER (PARTITION BY t.id) 
        THEN 1 END AS IsDifferent
    FROM YourTable t
) t
WHERE t.IsDifferent = 1;

CodePudding user response：

If you want to exclude from your result set only those rows which don't have repeated "Status" values, you can select ids that have the same COUNT(Status) and COUNT(DISTINCT Status)

SELECT * 
FROM tab 
WHERE ID IN (SELECT ID 
             FROM tab 
             GROUP BY ID 
             HAVING COUNT(DISTINCT Status) = COUNT(Status))

Check the demo here.