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?
CodePudding user response:
You can solve your problem with the window function ROW_NUMBER
, that will assign a unique number to each of your rows. Since you want:
- only one row for each "ID", then you can use the
PARTITION BY
clause on "ID" - the first "date", then you can use the
ORDER BY
clause on "date" ascendently
Once you get this ranking value, the rows to retrieve (for each "ID") will be the ones that have rank = 1 (the lowest "date" for each partition).
WITH cte AS (
SELECT *,
ROW_NUMBER() OVER(
PARTITION BY ID
ORDER BY date
) AS rn
FROM tab
)
SELECT ID,
pid,
method,
date
FROM cte
WHERE rn = 1
Check the demo here.