How could I return the next row(Or 8th row for example)

Question

Post reply

How could I return the next row(Or 8th row for example)

Ach-230297

Right there with Babe

Points: 759
More actions
September 25, 2005 at 7:26 am

#90005

Hi,

I want to return the next row in a select ... order by ... cursor.

I don't want to loop,just SQL,Do you know any solution?

For example: in Oracle we use rownum,is there any equivalent in SQL Server?

-Thanks

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

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

Vladan SSC-Insane Points: 22072 More actions · Answer 1

Hmm... I don't know why you are using a cursor, if you don't want to loop... but anyway, there is no equivalent is SQLS 2000. If I remember correctly, ROWNUM is one of the new things implemented in Yukon - at least in SELECT statements, don't know about cursors.

One possibillity would be to insert the ordered result into a temp table with IDENTITY column and then select based on value of that column. However, I don't know anything about the environment in which you will use it...

BTW, is the cursor really necessary?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

Ach, do a search through this forum. There have been several posts recently about how to get the nth row of a table. You should be able to find one that works for you.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Raghu Ravi SSC-Addicted Points: 420 More actions · Answer 3

from the Query u've posted. I think if u pass a particular number then u shd be getting the next number

for example, in a series of numbers like 232, 246,386, 356, 432, 550, 689, 890, 765, 345

if u pass 246 as an input , then it shd return the 345 as o/p. If this is ur exact problem then the Query will be something like this

select min(value) from temp where value > @inputvalue

if you are looking for a sol like this. Get the nth highest paid salary kinda stuff

check the below Query

create table temp

(

sal int ,

name varchar(10)

)

insert into temp values (1000,'A')

insert into temp values (2000,'B')

insert into temp values (5000,'C')

insert into temp values (6000,'D')

insert into temp values (7000,'E')

insert into temp values (6000,'F')

insert into temp values (5000,'G')

insert into temp values (1500,'H')

select * from temp a where 4 = (select count(distinct(sal)) from temp b where b.sal <= a.sal)

The above Query will retrieve the 4th highest salary getting person.

and if this is not u r looking for, then let me know bout the exact Problem of urs

Ach-230297 Right there with Babe Points: 759 More actions · Answer 4

Thanks for your replies but this is not my case.You can't apply min() function always.Assume that I want to order by <3_varchar_columns_here>.

I think I am not clear enough,I am not returning an explicit cursor( In Oracle we can't perform a SELECT within a procedure of function without an explicit cursor and I noticed that this is not true in SQL Server) so the only thing that I do is a *SELECT .... ORDER BY ...." within a procedure and there is no output parameter with cursor type.

-Regards

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

SELECT TOP 1 ... FROM .... WHERE pk NOT IN (SELECT TOP n pk FROM table ORDER BY <3_varchar_columns_here> )

ORDER BY <3_varchar_columns_here>

That should give you a good start.

HTH

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass