sqlsql-serverrow-number

How can I use ROW_NUM() to pick one instance where it doesn't have a second record?


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!


Solution

  • 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';