SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Mytable (
  Comment VARCHAR(60),
  ID INT  
)

INSERT Mytable
VALUES 
  ('SEND Final', 123456),
  ('SEND DRAFT', 123456),
  ('SEND FINAL', 456789),
  ('SEND Draft', 789456),
  ('SEND Final', 789456),
  ('SEND FINAL', 123789),
  ('SEND DRAFT', 456781),
  ('SEND FINAL', 456781),
  ('SEND DRAFT', 987654),
  ('SEND Final', 987654),
  ('SEND FINAL', 987321)
11 rows affected

-- Picks Draft regardless of ID

SELECT *, 
  MAX(CASE WHEN Comment LIKE '%Draft%' THEN 'Y' ELSE 'N' END) AS Draft
FROM Mytable

GROUP BY Comment, ID
ORDER BY ID ASC


Comment
ID
Draft


SEND DRAFT
123456
Y

SEND Final
123456
N

SEND FINAL
123789
N

SEND DRAFT
456781
Y

SEND FINAL
456781
N

SEND FINAL
456789
N

SEND Draft
789456
Y

SEND Final
789456
N

SEND FINAL
987321
N

SEND DRAFT
987654
Y

SEND Final
987654
N


-- Using ROW_NUMBER() I get ID's grouped and can tell which ID's
-- 'Draft' and 'Final' in 'Comment' column for each ID and which 
-- only have 'Final' (which is what I want)

SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) Row_num
FROM Mytable

GROUP BY Comment, ID
ORDER BY ID ASC


Comment
ID
Row_num


SEND DRAFT
123456
1

SEND Final
123456
2

SEND FINAL
123789
1

SEND DRAFT
456781
1

SEND FINAL
456781
2

SEND FINAL
456789
1

SEND Draft
789456
1

SEND Final
789456
2

SEND FINAL
987321
1

SEND DRAFT
987654
1

SEND Final
987654
2


SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) Row_num
FROM Mytable

WHERE Row_Num = 1 and Comment LIKE '%SEND Final%'  
  
GROUP BY Comment, ID
ORDER BY ID ASC
Msg 207 Level 16 State 1 Line 4
Invalid column name 'Row_Num'.

-- This gives me all Row_num = 1 but I only want Row_num = 1
-- where ID has only one comment that "LIKE '%SEND Final%'" and not a
-- value in comment that is "LIKE '%SEND Draft%'"

-- Unsure of logic to do this

WITH cte AS(
   SELECT
      Comment,
      ID,
      ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) Row_num
   FROM Mytable
)
SELECT * FROM cte
WHERE Row_num = 1


Comment
ID
Row_num


SEND Final
123456
1

SEND FINAL
123789
1

SEND DRAFT
456781
1

SEND FINAL
456789
1

SEND Draft
789456
1

SEND FINAL
987321
1

SEND DRAFT
987654
1

Comment	ID	Draft
SEND DRAFT	123456	Y
SEND Final	123456	N
SEND FINAL	123789	N
SEND DRAFT	456781	Y
SEND FINAL	456781	N
SEND FINAL	456789	N
SEND Draft	789456	Y
SEND Final	789456	N
SEND FINAL	987321	N
SEND DRAFT	987654	Y
SEND Final	987654	N