Oracle SQL - Returning multiple row results into one single row using LAG or LEAD

Question

I'm trying to figure out how to return 1 row for multiple row results.

Currently my code looks like the following:

select x.Reference,
x.date "Date1",
x.char "Char1",
lead(x.date, 1) OVER (PARTITION BY x.Reference ORDER BY x.date) as "Date2",
lead(x.char, 1) OVER (PARTITION BY x.Reference ORDER BY x.date) as "Char2",
lead(x.date, 2) OVER (PARTITION BY x.Reference ORDER BY x.date) as "Date3",
lead(x.char, 2) OVER (PARTITION BY x.Reference ORDER BY x.date) as "Char3"

from tbl x

The table has multiple entries for each x.Reference. For the first row of each x.Reference the row returns the desired result. However, as expected, it continues to output a row for each x.reference it's found. I'm trying to find a way to limit the output of these additional rows as the first row already have the data I need.

Thanks in advance.

Answer 1

Well, you can use row_number():

with x as (
      select x.Reference, x.date as "Date1", x.char as "Char1",
             lead(x.date, 1) OVER (PARTITION BY x.Reference ORDER BY x.date) as "Date2",
             lead(x.char, 1) OVER (PARTITION BY x.Reference ORDER BY x.date) as "Char2",
             lead(x.date, 2) OVER (PARTITION BY x.Reference ORDER BY x.date) as "Date3",
             lead(x.char, 2) OVER (PARTITION BY x.Reference ORDER BY x.date) as "Char3",
             row_number() over (partition by x.Reference order by x.date) as seqnum
      from tbl x
     )
select x.*
from x
where seqnum = 1;