SQL - How to get and sort multiple data in unique table-CodePudding

I have a query that return values based on a boolean column: if the id_crsp includes a boolean true AND false, then it is selected. Values of id_crsp that have only a true or false value are not selected.

From this result, I sort the id_crsp which have duplicates, and select only the one with the oldest date.

Database values :

id	idcrsp	date_false	boolean
1	100	01-01-2023	true
2	100	01-07-2022	false
3	200	01-06-2022	false
4	300	01-02-2023	true
5	300	01-08-2022	false
6	400	01-10-2022	false
7	100	01-01-2022	false
8	100	01-02-2022	false
9	100	01-11-2022	true

My actual request :

SELECT * 
FROM
    (SELECT 
         true_table.*, 
         ROW_NUMBER() OVER (PARTITION BY id_crsp ORDER BY date ASC) rn     
     FROM 
         mydb AS true_table 
     INNER JOIN 
         (SELECT * 
          FROM mydb 
          WHERE requalif = TRUE) AS false_table ON true_table.idcrsp = false_table.idcrsp 
                                                AND true_table.requalif = FALSE)

WHERE rn = 1

This returns:

id	idcrsp	date_false	boolean
7	100	01-01-2022	false
5	300	01-08-2022	false

I would like to enrich my request with :

new column for my select, with the most recent date for the idcrsp with true boolean
new column with the difference between this two dates in days

Return expected :

id	idcrsp	date_false	boolean	date_true	difference_in_days
7	100	01-01-2022	false	01-01-2023	365
5	300	01-08-2022	false	01-02-2023	180

01-01-2023 = idcrsp: 100, boolean: true, date: most recent

01-02-2023 = idcrsp: 300, boolean: true, date: most recent

Thanks for your help !

CodePudding user response：

For each idcrsp that have both boolean values, you want to bring the date of thte most recent true and false values. You can do this with aggregation, like so:

select idcrsp,
    max(case when not flag then date end) as date_false,
    max(case when     flag then date end) as date_true
from mydb
group by idcrsp
having min(flag) <> max(flag)

The having clause ensures that both boolean values are present in the group. Then we use conditional max()s to pick the relevant dates in each group.

How to compute the date difference in days highly depends on the database you are running. If you were running Postgres, from example, you would just substract the two expressions.

CodePudding user response：

Response works with MySql, but not with postgreSQL :

ExampleExceptionFormatter: exception message was: ERROR: function min(boolean) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.