How do I only return one row per ID, placing priority on approved?

Example: for Id 1 I only want the row that is approved and not the one that is pending.

Some Id's may only have 1 record for example ID 4 has just one record and is pending.

What I want is: IF status = approved and pending for the same Id then keep the approved record and not select the pending record

If status = pending then keep that record

CodePudding user response：

This will preferentially select Approved, then Pending, then everything else. If you don't want "everything else" just filter in the WHERE clause.

select id,
       name,
       date,
       status
  from (
        select *,
               row_number() over 
                 ( partition by id
                       order by case when status = 'Approved' then 1
                                     when status = 'Pending' then 2
                                     else 3
                                 end asc,
                                date
                 ) as first_by_date_with_approved_precedence
          from your_table
       ) tmp
 where first_by_date_with_approved_precedence = 1

CodePudding user response：

It could also be as easy as the following (provided status is not blank or null)

Select Top 1 with ties * 
 from  YourTable
 order by row_number() over (partition by id order by Status)

Results

ID  NAME    DATE    STATUS
1   Joe     01-22   Approved
2   Bill    02-22   Approved
3   John    01-22   Approved
4   Bob     02-22   Pending