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.

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

Comments
Agree

and also i want to get row position of some value.
Eg: there are 5 records in the table.
Stu_name . . Age
john...........25
smith..........26
kevin..........24
grehm.........45
david..........50

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...?

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
dr.Read()

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: http://technet.microsoft.com/en-us/library/ms187316.aspx

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

Edited 5 Years Ago by adam_k: n/a

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.

This question has already been answered. Start a new discussion instead.