how to select random numbers from a large table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to select random numbers from a large table

Author

Topic

vandana
Starting Member

29 Posts

Posted - 2013-03-28 : 00:42:46

Hi,

How to select random numbers(1,3,5,7,9......)
from a large table without using top,and without cte

can anyone suggest me the soultion

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-28 : 00:55:42

[code]
SELECT * FROM employees
WHERE (ABS(CAST(
(BINARY_CHECKSUM(*) *
RAND()) as int)) % 100) < 10[/code]

vandana
Starting Member

29 Posts

Posted - 2013-03-28 : 00:59:50

am getting only 2 rows
and that to 3 ,4
but i want the rows 1,3,5,7,9.....

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-28 : 01:03:31

do you need odd number fo rows?

vandana
Starting Member

29 Posts

Posted - 2013-03-28 : 01:06:53

yes

vandana
Starting Member

29 Posts

Posted - 2013-03-28 : 01:16:20

Hey i got it

thanks

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-28 : 01:20:02

there are multiple ways... post your solution... It will help others who has that kind of problem

vandana
Starting Member

29 Posts

Posted - 2013-03-28 : 01:24:25

1. SELECT * FROM(
SELECT ROW_NUMBER()OVER (ORDER BY empno)AS ROW,* FROM emp)
A WHERE ROW%2=1

2. Select * from emp where (empno% 2) = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources