CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3374990
user3374990

Reputation: 21

How do I get nth number of records from 100 records by select query in SQL Server?

I want to select nth record randomly from 100 of records by using select query in SQL Server

Upvotes: 2

Views: 130

Answers (2)

Naveen
Naveen

Reputation: 1502

In the below code @R produces the random number and it is used by the CTE to pick the random row. 

DECLARE @Upper INT;
DECLARE @Lower INT;
SET @Lower = 1   
SET @Upper = 100


DECLARE @R INT = (SELECT @Lower + CONVERT(INT, (@Upper-@Lower+1)*RAND()))  

;WITH myTableWithRows AS (
            SELECT (ROW_NUMBER() OVER (ORDER BY myTable.SomeField)) as row,*
            FROM myTable)
        SELECT * FROM myTableWithRows WHERE row = @R

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Here is a one-query solution:

select top 1 t.*
from table t
order by newid();

Upvotes: 2

Related Questions