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 |