I am trying to use ROW_NUM() to get me instances of ID's that have only 1 associated comment value which is like 'SEND Final' and that's it.
This is an example of the records in a table:
Comment | ID |
---|---|
SEND Final | 1 |
SEND Draft | 1 |
SEND Final | 2 |
SEND Draft | 2 |
SEND Final | 3 |
After running my code (below output table) I expect this to be returned since it is the only record in the table that has just one instance of comment that is like 'SEND Final' (No record/value like 'SEND Draft' associated with the ID).
Comment | ID |
---|---|
SEND Final | 3 |
Here is what I have now that successfully partitions how I want it to, but I am having trouble with the logic of choosing the records that fit my criteria.
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) Row_num
FROM Mytable
GROUP BY Comment, ID
ORDER BY ID ASC
I've made a dbfiddle with some test data and also code. It is found Here
Any help is greatly appreciated or if I am going about this the wrong way, please let me know!
Not sure why you are using row_number
when you want a count
?
WITH cte AS (
SELECT
Comment,
ID,
COUNT(*) OVER (PARTITION BY ID) count_num
FROM Mytable
)
SELECT *
FROM cte
WHERE count_num = 1
-- Maybe the following line is required? The sample data isn't clear on that
AND Comment = 'SEND Final';