how to get 10th record from sql server

[weeraa]

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.

[GeekByChoiCe]

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

[weeraa]

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

[Pgmer]

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

[weeraa]

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

[adam_k]

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.

[Pgmer]

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

[weeraa]

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.