Reputation: 85
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
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
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