how to get 10 th record from record set using SQL server? there are a lot of code in the internet that can get first 10 results using "TOP" keyword in sql. but i want to get 10th row valus. I'm using VB 2008. please help me....
Regards Pra$ad.

Recommended Answers

All 7 Replies

Try this:

select * from (select Row_Number() over (order by [ID]) as RowIndex, * from [DB-Name].[dbo].[Table-Name]) as Sub Where Sub.RowIndex =10

commented: Agree +7

and also i want to get row position of some value.
Eg: there are 5 records in the table.
Stu_name . . Age

i want to get the row position of "kevin". it is in 3rd row. so the output should be 3. what is the method for getting this with SQL...?

Select @@rowcount from table where stu_name like ='kevin'

This is my code in vb 2008

sqlcommand = New SqlCommand()
sqlcommand.Connection = sqlcon
sqlcommand.CommandType = CommandType.Text
sqlcommand.CommandText = "Select @@rowcount from stuDetails where stu_name ='kevin'"
dr = sqlcommand.ExecuteReader

but how to assign row position to a variable...?

Select @@rowcount from table where stu_name like ='kevin'

Sorry, but I disagree. @@rowcount is a system variable that holds the number of records affected by the previous statement. It is used to verify a succesful update/delete/insert or a select that returned result even if the result was NULL.
Read more about it here:

The correct answer to get 3 for Kevin is again Row_Number(), used with a different where (stu_name= 'Kevin') from GeekByChoiCe's example.

Yes i was wrong.. Sorry.
Thanks Adam. :)

This is the SQL code that i used in VB.NET and it works.

SELECT * FROM(SELECT *, ROW_NUMBER() OVER (ORDER BY idx ASC) AS RowNum  FROM Table_Name) Table_Name WHERE RowNum = 10

thanks GeekByChoiCe... i did my work according to to your code.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.