sql - Pulling multiple entries based on ROW_NUMBER

Question

I got the row_num column from a partition. I want each Type to match with at least one Sent and one Resent. For example, Jon's row is removed below because there is no Resent. Kim's Sheet row is also removed because again, there is no Resent. I tried using a CTE to take all columns for a Code if row_num = 2 but Kim's Sheet row obviously shows up because they're all under one Code. If anyone could help, that'd be great!

Edit: I'm using SSMS 2018. There are multiple Statuses other than Sent and Resent.

What my table looks like:

+-------+--------+--------+---------+---------+
| Code  | Name   | Type   | Status  | row_num | 
+-------+--------+--------+---------+---------+
|   123 | Jon    |  Sheet |    Sent |     1   |           
|   221 | Kim    |  Sheet |    Sent |     1   |          
|   221 | Kim    |   Book |  Resent |     1   |            
|   221 | Kim    |   Book |    Sent |     2   |            
|   221 | Kim    |   Book |    Sent |     3   |  
+-------+--------+--------+---------+---------+    

What I want it to look like:

+-------+--------+--------+---------+---------+
| Code  | Name   | Type   | Status  | row_num | 
+-------+--------+--------+---------+---------+     
|   221 | Kim    |   Book |   Resent|     1   |            
|   221 | Kim    |   Book |    Sent |     2   |            
|   221 | Kim    |   Book |    Sent |     3   |  
+-------+--------+--------+---------+---------+  

Here is my CTE code:

WITH CTE AS 
(
    SELECT * 
    FROM #MyTable
)
SELECT * 
FROM #MyTable
WHERE Code IN (SELECT Code FROM CTE WHERE row_num = 2)

question from:https://stackoverflow.com/questions/65945437/pulling-multiple-entries-based-on-row-number

Answer 1

If sent and resent are the only values for status, then you can use:

select t.*
from t
where exists (select 1
              from t t2
              where t2.name = t.name and
                    t2.type = t.type and
                    t2.status <> t.status
             );

You can also phrase this with window functions:

select t.*
from (select t.*,
             min(status) over (partition by name, type) as min_status,
             max(status) over (partition by name, type) as max_status
      from t
     ) t
where min_status <> max_status;

Both of these can be tweaked if other status values are possible. However, based on your question and sample data, that does not seem necessary.