making an select with case-CodePudding

I have this table

Id	Date	Location	Serial	Status
1	2022-10-09	Berlin	204408	Rejected
2	2022-10-09	Roma	233970	Approved
3	2022-10-09	Paris	233971	Approved
4	2022-10-09	Paris	233971	Rejected
5	2022-10-09	Berna	233974	Approved

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:

Id	Date	Location	Serial	Status
1	2022-10-09	Berlin	204408	Rejected
2	2022-10-09	Roma	233970	Approved
3	2022-10-09	Paris	233971	Approved
5	2022-10-09	Berna	233974	Approved

CodePudding user response：

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 |
 ---- ------------ ---------- -------- ----------

CodePudding user response：

You can write this query (available in all DBMS known to me):

SELECT *
FROM YourTable AS T1
WHERE T1.Status = 'Approved'
  OR NOT EXISTS (SELECT * FROM YourTable AS T2 WHERE T1.Date=T2.Date AND T1.Location=T2.Location AND T1.Serial=T2.Serial AND T2.Status='Approved')

Take all Approved and all others where Approved does not exist.