Select first value then fill with null for unique id-CodePudding

I'm using a Postgres database.

I've been trying to resolve this for hours and read dozens of topics with no result yet

Since I don't know how to explain my issue with words, here is what I need by example :

My query is

select distinct chiffre_affaires.contrat_pentete_id,
       chiffre_affaires.chiffre_affaires_id,
       chiffre_affaires.chiffre_affaires_montant_total
from chiffre_affaires;

Current output :

contrat_pentete_id	chiffre_affaires_id	chiffre_affaires_montant_total
1	1	111.7848
1	2	111.7848
1	3	111.7848
1	4	111.7848
1	5	111.7848
1	6	111.7848
2	7	90
2	8	90
2	9	90
2	10	90

Expected output :

null values can be replaced by 0, both null or 0 would work

contrat_pentete_id	chiffre_affaires_id	chiffre_affaires_montant_total
1	1	111.7848
1	2	null
1	3	null
1	4	null
1	5	null
1	6	null
2	7	90
2	8	null
2	9	null
2	10	null

Thank you in advance for any help !

CodePudding user response：

Trying to understand what you want to achieve : for a group of rows with same contrat_pentete_id, ordered by chiffre_affaires_id ASC, you want to display the chiffre_affaires_montant_total value for the first row, and NULL for the next rows. If so, you can try this :

SELECT DISTINCT 
       ca.contrat_pentete_id,
       ca.chiffre_affaires_id,
       CASE
         WHEN ca.chiffre_affaires_id = first_value (ca.chiffre_affaires_id) OVER (ORDER BY ca.chiffre_affaires_id)
         THEN ca.chiffre_affaires_montant_total
         ELSE NULL
       END AS ca.chiffre_affaires_montant_total
FROM chiffre_affaires AS ca
ORDER BY ca.contrat_pentete_id, ca.chiffre_affaires_id

CodePudding user response：

Thanks to Edouard H. I finally wrote a script that did the job.

Here is the solution :

SELECT DISTINCT ca.contrat_pentete_id,
                ca.chiffre_affaires_id,
                ca.chiffre_affaires_annee_mois,
                CASE
                    WHEN ca.chiffre_affaires_id =
                         first_value(ca.chiffre_affaires_id) OVER (PARTITION BY ca.contrat_pentete_id ORDER BY ca.chiffre_affaires_annee_mois)
                        THEN ca.chiffre_affaires_montant_total
                    END AS montant_facture
FROM chiffre_affaires AS ca
ORDER BY ca.contrat_pentete_id;