title: How to compare values in several fields for each ID in a table
body: <p>I've a table that contains alert information for replacement orders.(br)(br)
  I need to confirm for each alert that for each Order ID in the alert ID:
       The Buyer names are the same
       The paid date is different between each alert.</p>
  
  <pre><code>Alert_ID | Order_ID | Buyer_ID | Item  | Quantity | Paid_date
  01       | 001A     | Adam     | Apple | 2        | 01/01/2019
  01       | 001A     | Adam     | Orange| 3        | 01/01/2019
  01       | 001B     | Adam     | Apple | 4        | 01/03/2019
  01       | 001B     | Adam     | Orange| 3        | 01/03/2019
  01       | 001C     | Adam     | Apple | 3        | 01/07/2019
  01       | 001C     | Adam     | Orange| 3        | 01/07/2019
  02       | 002A     | Pam      | Banana| 2        | 01/21/2019
  02       | 002A     | Pam      | Grapes| 1        | 01/21/2019
  02       | 002B     | Pam      | Banana| 2        | 01/30/2019
  02       | 001B     | Pam      | Grapes| 4        | 01/30/2019
  04       | 004A     | Dave     | Apple | 2        | 01/01/2019
  04       | 004B     | Mary     | Apple | 3        | 01/01/2019
  </code></pre>
  
  <p>Initially this was a 1:1 relationship of replacement claims to original claims. <br><br>I split the table in two by alert_id with the replacement order in one sub query and the original order In the other, then I could compare all the fields I wanted between alerts and orders.</p>
  
  <pre><code>select
          *
  from
          (
                  with repl as(
                          select
                                  Alert_ID, Order_ID, Buyer_ID, Paid_date
                                  ROW_NUMBER () over(partition by alert_id order by alert_id, Order_id) AS RN
                          from &lt;MY ALERT TABLE&gt;
                  )
                  SELECT * FROM repl WHERE RN = 1
          ) A
          LEFT JOIN 
          (
                  with repl as(
                          select
                                  Alert_ID, Order_ID, Buyer_ID, Paid_date
                                  ROW_NUMBER () over(partition by alert_id order by alert_id, Order_id) AS RN
                          from &lt;MY ALERT TABLE&gt;
                  )
                  SELECT R1.* 
                  FROM repl R1 
                  LEFT OUTER JOIN repl R2
                  ON R1.ALERT_ID = R2.ALERT_ID AND R1.RN &lt; R2.RN
                  WHERE R2.RN IS NULL
          ) B
          ON A.ALERT_ID = B.ALERT_ID
  WHERE
  
          a.order_id=b.order_id
          and a.buyer_id &lt;&gt; b.buyer_id
          and a.paid_date &gt; b.paid_date
  </code></pre>
  
  <p>This worked fine for 1:1, but with 1:n I'm loosing out on all orders that appear in between the first and last row number.</p>
  
  <p>Expected Result:
  <br>Return exceptions to the rules</p>
  
  <pre><code>Alert_ID | Order_ID | Buyer_ID | Item  | Quantity | Paid_date
  04       | 004A     | Dave     | Apple | 2        | 01/01/2019
  04       | 004B     | Mary     | Apple | 3        | 01/01/2019
  </code></pre>