CODEWITHSUNDEEP
sql-server-2008rank
Snowy
Snowy

Reputation: 6122

Update based on sequence/rank in SQL Server 2008?

Table looks like this:

create table #rankme (rankmeid int identity(1000, 1) primary key, 
                      step int null, checkvalue int null)

insert into #rankme values ( 10 , 1 )
insert into #rankme values ( 15 , null )
insert into #rankme values ( 20 , null )
insert into #rankme values ( 40 , null )

select * from #rankme order by step,checkvalue

Taking step as a parameter, I am trying to find out if the requested checkvalue for the one before the step I asked for is null.

So I want to select where step=20 and get NULL.

And I want to select where step=15 and get a 1.

I was trying to come up with something based on "rank-1" but so far no cigar.

Help?

Upvotes: 1

Views: 319

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

declare @step int = 15

select top(1) R.checkvalue
from #rankme as R
where R.step < @step
order by R.step desc

Upvotes: 2

Related Questions