1

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?

lemon
  • 14,875
  • 6
  • 18
  • 38
moikoi
  • 25
  • 8
  • can you share the query you mention? maybe it just needs some quick fix @koimoi – lemon Jun 12 '22 at 18:47
  • 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 – moikoi Jun 12 '22 at 19:17
  • Can you tell the reason of using the `LEAD` window function? Is there a requirement not specified in the post? Also how do you choose the value for the `method` field? @koimoi – lemon Jun 12 '22 at 19:24
  • 1
    Yes this table had multiple IDs and I need the min date and the corresponding method for all the IDs. – moikoi Jun 12 '22 at 19:26

1 Answers1

1

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.

lemon
  • 14,875
  • 6
  • 18
  • 38