sqlpostgresqldata-partitioning

PSQL determine the min value of date depending on another column


The input table looks like this:

ID pid method date
111 A123 credit_card 12-03-2015
111 A128 ACH 11-28-2015

Now for the ID = 111, I need to select the MIN(date) and see what the method of payment for it is. I need the output table to be as follows:

ID. method date
111 ACH 11-28-2015

I've tried using a window function to get the minimum date and use a LEAD in the subquery, still does not give me what I want.

WITH fd AS( 
    SELECT DISTINCT ID, 
                    method , 
                   (MIN(date) OVER(PARTITION BY method)) AS first_dt 
    FROM table 
    WHERE id = 111
) 
SELECT DISTINCT fd.ID, 
                method, 
                LEAD(first_dt) OVER(ORDER BY fd.ID) AS first_method 
FROM fd

Can anyone please help me with this?


Solution

  • An alternate solution, prompted by Cignitor's comment.

    We can use the FIRST_VALUE function to get the first value of any particular column subject to any defined window. In this case, we want to consider each ID separately so we partition on that column, and we want to consider the 'first' element when ordering by date.

    SELECT DISTINCT
        ID
      , FIRST_VALUE(method) OVER w
      , FIRST_VALUE(date) OVER w
    FROM tab
    WINDOW w AS (PARTITION BY ID ORDER BY date)