Matthew
Matthew

Reputation: 85

SQL - How can I select the next value in a column?

In SQL Server 2008 r2 I have a column 'Activity_ID' that's an integer and can have a range of values (i.e. 20, 21, 22, 28, 29, 37, 38, 41).

I have no way of knowing what Activity_ID will be next.

Is there any way to check for the next highest Activity_ID (and then do X once I have found it)?

M

Upvotes: 0

Views: 162

Answers (2)

APH
APH

Reputation: 4154

In SQL Server 2012 or later, you would use lag or lead. In 2008, you can simulate the same behavior with row number. I am guessing here because your question didn't come with sample data, but it might look something like this:

;with CTE as 
  (select *
  , Row_Number() over (partition by Name order by ActivityCode) as RN 
  from MyTable)

select a.*, b.activitycode as NextHighestCode 
from CTE a
left join CTE b
on a.name = b.name
and a.RN = b.RN - 1

This will return the values in your table (replace name with whatever actual columns you have), and the next highest activity code for each row. This may not handle ties in the way that you would expect, so you may want to add more columns to the partition clause if you are expecting them.

Upvotes: 1

Thiago Custodio
Thiago Custodio

Reputation: 18387

  declare @tb table
  (
    orderNumber int identity(1,1),
    value int
  )

  insert into @tb values(20)
  insert into @tb values(21)
  insert into @tb values(22)
  insert into @tb values(28)
  insert into @tb values(29)
  insert into @tb values(37)
  insert into @tb values(38)
  insert into @tb values(41)



  declare @v table(
    activity_id int
  )

  insert into @v values(37)

    select 
        (select top 1 value from @tb t1 where  t1.value > v.activity_id)
     from @v v

Upvotes: 0

Related Questions