Post reply

Select next row value

  • November 14, 2012 at 8:58 am

    #393602

    I know there is a function in sql to grab the a field value for the next row. I have seen it before but for the name of God I cannot think of the name of the function.

    for example if I have

    id FN LN

    1 Joe Smith

    2 patrick morris

    3 john tucker

    and I do a "select LN, nextrowvalue(FN) from table name" should give me the output

    Patrick Smith

    John Morris

    NULL Tucker

  • November 14, 2012 at 9:14 am

    #1558999

    SQL Server 2012 only...

    LEAD(FN) OVER(ORDER BY ID),LN

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • November 14, 2012 at 9:16 am

    #1559002

    I have seen one for SQL 2008 R2, just cannot remember. Arrrgggr!!!

  • November 14, 2012 at 9:21 am

    #1559007

    HildaJ (11/14/2012)

    I know there is a function in sql to grab the a field value for the next row. I have seen it before but for the name of God I cannot think of the name of the function...

    There is no such thing in the RDBMS table as "next row"! So, don't think God will help you there :hehe:

    Based on what you describe is you want to get value for the record with next highest id

    You can use new windowed function LEAD, but it's only available in SQL2012

    In SQL2008 you can do something like that:

    create table #table (id int, FN varchar(20), LN varchar(20))

    insert #table

    select 1, 'Joe', 'Smith'

    union select 2, 'patrick', 'morris'

    union select 3, 'john', 'tucker'

    union select 5, 'missed', 'one'

    select T1.id, T1.FN, nxt.LN, nxt.id

    from #table T1

    outer apply (select top 1 T2.id, T2.LN

    from #table T2

    where T2.id>T1.id order by T2.id) nxt

    See, I have added the record with id 5 and there is no record with id 4....

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • November 14, 2012 at 9:26 am

    #1559011

    I'll give it a try. Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply