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

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

Error Msg

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

[prabirchoudhury]

try with out "with (NOLOCK)"

and that gonna wrk

select * FROM employee order by RAND() limit 79

[mwasif]

Moved to SQL Server forum.

[ddtpmyra]

prabirchoudhury, it's still not working same error of

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]

@ck9663
im having syntax error on below sql query

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

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