Select every 79th record

ddtpmyra

I have 400K records on my table. Now I wanted you pull out random records that falls every 79th records from top to bottom. How can you do that on sql?

Your help is much appreciated.
thanks,
DM

prabirchoudhury

yes .. try this

$sql = "select * from table_name order by RAND() limit 79 "

ddtpmyra

just a clarification i wanted to select the records on my table that has 437K row any records that falls on every 79th records. To sum up i should get aroun 5,500 number of records filtering it from top to bottom.

im using ms sql server and i had error

SQL

Quote:

select * FROM employee with(nolock) order by RAND() limit 79

Error Msg

Expand|Select|Wrap|Line Numbers

 Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'limit'.

prabirchoudhury

try with out "with (NOLOCK)"

and that gonna wrk

Quote:

select * FROM employee order by RAND() limit 79

mwasif

Moved to SQL Server forum.

ddtpmyra

prabirchoudhury, it's still not working same error of

Quote:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'limit'.

ck9663

Use ROW_NUMBER() function as inside a subquery and put a WHERE on the outer query where RownumberReturned mod 79 = 0

That's a pseudo-code.

Happy Coding!

--- CK

ddtpmyra

Quote:

Originally Posted by ck9663

Use ROW_NUMBER() function as inside a subquery and put a WHERE on the outer query where RownumberReturned mod 79 = 0

That's a pseudo-code.

Happy Coding!

--- CK

im having syntax error on below sql query

Quote:

Msg 207, Level 16, State 1, Line 4
Invalid column name 'RowNumber'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'RowNumber'.

Expand|Select|Wrap|Line Numbers

 SELECT *,

    ROW_NUMBER() OVER (ORDER BY RAND()) AS 'RowNumber'

    FROM users with (nolock)

    where RowNumber between  79 and 0

ck9663

Use it as a subquery instead.

Happy Coding!!!

--- CK

Select every 79th record

re: Select every 79th record

re: Select every 79th record

re: Select every 79th record

re: Select every 79th record

re: Select every 79th record

re: Select every 79th record

re: Select every 79th record

re: Select every 79th record

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

/bytes/about