SQL Server Performance

LAG & LEAD Function in MS Sql Server

Discussion in 'SQL Server 2005 General Developer Questions' started by Sandy, Apr 9, 2009.

  1. Sandy New Member

    Hi All,
    One of my friend just now asked me in Sql LAG & LEAD Function exists or not?
    for ex:
    {
    ---- LAG (Min(id), 1, 0) OVER (ORDER BY Min(type)) AS prev,
    ---- LEAD (Min(id), 1, 0) OVER (ORDER BY Min(type)) AS next
    }
    I told no..then he asked me to solve one of his query....
    I am just doing R&D on it...
    Can you please help me for this...create
    table #temp(t1
    int,seq
    int)insert
    into #tempselect
    5, 98 union all
    select
    5, 99 union all
    select
    5, 100 union all
    select
    5, 101 --select
    * from #temp----select
    case when sign(seq - 100) > 0 then 'next' else 'prev' end as dir,
    case when sign(seq - 100) > 0 then min(seq) else max(seq) end as seqfrom
    #tempwhere t1 = 5
    and seq <> 100group
    by sign(seq - 100)order
    by sign(seq - 100)----drop
    table #temp
    ---
    the above query is only giving for one data when seq = 100.
    can we do it for others too...??
    I hope by EOD, we can achieve it....
    Can anybody give me hints onobove things...??
    Thanks,
    Sandy.
  2. satya Moderator

Share This Page