SQL Multi Conditional CASE WHEN issues

Question

I am encountering a problem when trying to use a CASE WHEN for the following scenario:

I have a table with more than 3 columns but these 3 are the only relevant ones: order_number, payment_method, order_date.

There can be multiple "orders" per order_number. There will never be two of the same order_date

Order Number	Payment Method	Order Date
120	Cash	01/01/2021
175	Credit Card	01/02/2021
209	Cash	01/03/2021
209	Credit Card	01/04/2021
209	Personal Check	01/05/2021
209	Credit Card	01/06/2021
209	Cash	01/07/2021
209	Personal Check	01/08/2021
277	Credit Card	01/09/2021
301	Cash	01/10/2021
333	Personal Check	01/11/2021
333	Cash	01/12/2021
333	Cash	01/13/2021
333	Personal Check	01/14/2021
400	Credit Card	01/15/2021
551	Credit Card	01/16/2021
551	Cash	01/17/2021
680	Personal Check	01/18/2021

I am trying to consolidate it so that I have a list with only 1 row per order number, based on the following logic:

1. If there is only 1 order number - return the order date
2. If there are >1 alike order numbers and any of those orders was paid with a credit card - return the most recent order date that was paid by credit card
3. If there are >1 alike order numbers and none of the orders were paid with a credit card - return the most recent order date.

And the goal would look like this:

Order Number	Payment Method	Order Date
120	Cash	01/01/2021
175	Credit Card	01/02/2021
209	Credit Card	01/06/2021
277	Credit Card	01/09/2021
301	Cash	01/10/2021
333	Personal Check	01/14/2021
400	Credit Card	01/15/2021
551	Credit Card	01/16/2021
680	Personal Check	01/18/2021

No duplicate order numbers, and when there were duplicate order numbers, the logic was followed and that row was pulled.

I tried various methods of CASE WHEN, but always fail when it comes to applying a condition on the THEN part.

Answer 1

As a general method, you can use row_number():

select o.*
from (select o.*,
             row_number() over (partition by order_number
                                order by (case when payment_method = 'Credit Card' then 1 else 2 end),
                                         order_date desc
                               ) as seqnum
      from orders o
     ) o
where seqnum = 1;

This is generic SQL and should work in any database. However, depending on your database there might be shortcuts.