Apply lead function on the first value

Question

So I used the lead function and it works great for leading the values of rows 2 and 3 as you can see below

Here is the code to generate that table in sql.

drop table timetable;

create table timetable(
names varchar(50),
timestart integer
);

insert into timetable values ('NAV',1);
insert into timetable values ('Jim',2);
insert into timetable values ('MIC',3);

select names
,timestart
, Lead(timestart) Over (order by timestart) as endtime
from timetable;

However I want to write a code that automatically creates a row above with no name and no timestart but with a endtime of 1, since that is the first row's timestart value. Below I have envisioned of what I want it to look like.

Correct the rows are not sorted, what I am trying to do is lead every single value not just the vales below row 1. Meaning in a regular lead function 2nd rows's value (2) would be attached to the row above (the 1st row)'s last column, which is correct. However I also want the first row's value (1) to attach to a row above even if the row does not exist, hench write code that generates a row above and attach the value (1) to the last column. PS you are right, I do need to sort the rows, Thank You. — Nick Ani, Jul 05 '19 at 01:54
"...below row 1..." -- there's no such thing as row 1 or 2, or 3. Table rows do not have inherent order. You specify the order you prefer when retrieving them. In simple words, a database table **is not** an Excel sheet. — The Impaler, Jul 05 '19 at 16:21

The Impaler · Answer 1 · 2019-07-05T16:16:08.193

1

Assuming you are sorting by timestart you can do:

select * 
from (
  select 
    names, 
    timestart, 
    lead(timestart) over (order by timestart) as endtime
  from timetable
  union all
  select null, null, min(timestart) from timetable
) x
order by case when timestart is null then 0 else 1 end, timestart

Result:

names   timestart  endtime
------  ---------  -------
<null>     <null>        1
NAV             1        2
Jim             2        3
MIC             3   <null>

edited Jul 05 '19 at 16:16

answered Jul 05 '19 at 01:02

The Impaler

45,731
9
39
76

This does generate the correct graph, but I need it to select what ever value is contained in the first row's timestart column. – Nick Ani Jul 05 '19 at 01:57
Fixed. However, your query still lacks ordering. You should make sure it includes an `ORDER BY` clause for all this logic to make sense. – The Impaler Jul 05 '19 at 16:16

score 0 · Answer 2 · answered Jul 05 '19 at 02:35

0

just add the union all to your query

select   names
        ,timestart
        ,Lead(timestart) Over (order by timestart) as endtime
from    timetable

union all

select  null
       ,null
       ,min(timestart) 
from    timetable

order by timestart

answered Jul 05 '19 at 02:35

Squirrel

23,507
4
34
32

score 0 · Answer 3 · answered Jul 05 '19 at 17:00

This might be a good approach from a code duplication standpoint. I also take it that you probably don't know what that first timestart is going to be in advance. The way to use this is to drop your main query into the inner CTE. The new row is then added with a timestart value copied from that dataset.

with q as ( -- your real query here
    select names, timestart, endtime,
    from timetable ...
)
select null as names, null as timestart, min(timestart) as endtime from q
union all
select names, timestart, endtime from q
order by timestart;

This is very similar to The Impaler's solution but I think this might give you a small advantage if the logic is a lot more complicated than what you've written above.

Apply lead function on the first value

3 Answers3