By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Select every 79th record

ddtpmyra
100+
P: 333
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
May 15 '09 #1
Share this Question
Share on Google+
8 Replies


prabirchoudhury
100+
P: 162
yes .. try this

$sql = "select * from table_name order by RAND() limit 79 "
May 15 '09 #2

ddtpmyra
100+
P: 333
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
Expand|Select|Wrap|Line Numbers
  1. Msg 102, Level 15, State 1, Line 1
  2. Incorrect syntax near 'limit'.
  3.  
May 15 '09 #3

prabirchoudhury
100+
P: 162
try with out "with (NOLOCK)"

and that gonna wrk

select * FROM employee order by RAND() limit 79
May 16 '09 #4

mwasif
Expert 100+
P: 801
Moved to SQL Server forum.
May 16 '09 #5

ddtpmyra
100+
P: 333
prabirchoudhury, it's still not working same error of
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'limit'.
May 18 '09 #6

ck9663
Expert 2.5K+
P: 2,878
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
May 18 '09 #7

ddtpmyra
100+
P: 333
@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'.
Expand|Select|Wrap|Line Numbers
  1. SELECT *,
  2.     ROW_NUMBER() OVER (ORDER BY RAND()) AS 'RowNumber'
  3.     FROM users with (nolock)
  4.     where RowNumber between  79 and 0
Sep 8 '09 #8

ck9663
Expert 2.5K+
P: 2,878
Use it as a subquery instead.

Happy Coding!!!

--- CK
Sep 10 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.