Find the minimum date by user and add it as a boolean column in SQL-CodePudding

I have

SELECT 
    id, email, voucher_id, created_at_reservation, reservation_id
FROM
    (SELECT *
     FROM users
     LEFT JOIN 
         (SELECT 
              status, user_id, voucher_id, 
              created_at AS created_at_reservation, id AS reservation_id
          FROM 
              reservations 
          WHERE 
              reservations.status = 'paid') AS r ON r.user_id = users.id) AS u

And I want the minimum u.created_at_reservation by u.id, I would like to have a new column that is TRUE if that row contains the minimum u.created_at_reservation for that u.id

CodePudding user response：

Unless you are using some ancient DBMS, it's probably easiest to use a window function:

SELECT id, email, voucher_id, created_at_reservation, reservation_id
     , MIN(created_at_reservation) OVER (PARTITION BY id) as min_reservation  
[...]

For the boolean column you can add another outermost level:

SELECT ..., created_at_reservation = min_reservation as new_col
FROM (  
   SELECT id, email, voucher_id, created_at_reservation, reservation_id
     , MIN(created_at_reservation) OVER (PARTITION BY id) as min_reservation  
   [...]
)