making an select with case

I have this table

How to write a select when i have 2 records with Same Location Same date and same Serial but different Status to show me only the Approved Status.

Also i have to show the other results as well like this:

>Solution :

Use the row_number() function with a partition by clause to assign a sequential rank number for each partition, then select accordingly.

Using PostgreSQL

WITH t
     AS (SELECT id,
                date,
                location,
                serial,
                status,
                Row_number()
                  OVER (
                    partition BY date, location, serial
                    ORDER BY status ASC) AS rn
         FROM   #have)
SELECT id,
       date,
       location,
       serial,
       status
FROM   t
WHERE  rn = 1 

+----+------------+----------+--------+----------+
| id |    date    | location | serial |  status  |
+----+------------+----------+--------+----------+
|  1 | 2022-10-09 | Berlin   | 204408 | Rejected |
|  5 | 2022-10-09 | Berna    | 233974 | Approved |
|  3 | 2022-10-09 | Paris    | 233971 | Approved |
|  2 | 2022-10-09 | Roma     | 233970 | Approved |
+----+------------+----------+--------+----------+