The input table looks like this:

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:

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.