1

Considering the following simple example:

select lead(c) over (partition by a order by a)
from (values 
      (1,'1','date1'),
      (1,'2','date1'),
      (2,null,'date2')
) t(a,b,c)

The first two rows are records with the same ID (column a) and "date1" (column c). The ID duplication exists due to an unnest operation made in an array column that might have different sizes or number of elements. The unnest operation results in column b.

Now, I want to get the lead date of the next ID, "date2". The query I wrote outputs "date 1" for the first record, but I actually want to get "date 2". I'm not interested in the static solution to this problem, which would be lead(c,2) over (order by a), but on how to get the lead of a dynamic sized window.

Expected output:

date2
date2
NULL
  • I removed the mysql and sql-server tag because neither of them has arrays or an unnest operation on arrays – a_horse_with_no_name Aug 24 '19 at 6:22
  • You probably want order by b (but then ordering on strings that contain numbers is always full of surprises, so maybe you want order by cast(b as int). Or maybe including the array index in the output of unnest using with ordinality might help. But without more details this is hard to answer – a_horse_with_no_name Aug 24 '19 at 6:22
  • Could be a solution row_numbering it and get rows number = 1? – McNets Aug 24 '19 at 11:21
0

You can specify a window that excludes the current group:

SELECT first_value(c) OVER w
FROM (VALUES
      (1,'1','date1'),
      (1,'2','date1'),
      (2,null,'date2')
) AS t(a,b,c)
WINDOW w AS (
   ORDER BY a
   ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP
);

 first_value 
-------------
 date2
 date2

(3 rows)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.