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