Random selection of rows from a 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

SQL Server 2000 Forums

SQL Server Development (2000)

Random selection of rows from a table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-10-27 : 07:59:15

Sai Gopal writes "Hi,

I went through some related articles in here to select some rows randomnly from a table. I have been able to use it this way...

select id from foo order by newid() -> this works for me.

But what I needed was to convert the IDs into a comma separated list. Hence I modified the query as follows:

DECLARE @tmpCSV varchar(4000)
SET @tmpCSV = ''
SELECT @tmpCSV = @tmpCSV + ',' + CAST(ID AS varchar) FROM foo order by newid()
PRINT @tmpCSV

The above query only returns me the first id. But subsequent id's are not joined in the csv list!

However, if u change the query as:
DECLARE @tmpCSV varchar(4000)
SET @tmpCSV = ''
SELECT @tmpCSV = @tmpCSV + ',' + CAST(ID AS varchar) FROM foo order by id
PRINT @tmpCSV

This one does work.

I am not able to understand why order by newID() does not work.

Thanks,
Sai Kodi"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-27 : 08:39:30

[code]DECLARE @tmpCSV table (foobar varchar(10))

insert into @tmpCSV
SELECT CAST(ID AS varchar) + ',' FROM foo order by newid()
[/code]

now you have a temp table with IDs with ',' appended to them.
you can simply concatenate from there. Give it a few minutes and St.Peter will come up with an even better solution.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-27 : 09:09:11

Make use of derived table:

DECLARE @tmpCSV varchar(4000)
SET @tmpCSV = ''
SELECT @tmpCSV = @tmpCSV + ',' + CAST(ID AS varchar) FROM 
(select top 100 percent ID from foo order by newid()) as test
PRINT @tmpCSV

Harsh Athalye
India.
"Nothing is Impossible"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-27 : 09:11:45

SNIPED This will work in SQL 2000

declare @c varchar(8000)
select
@c = coalesce(@c, '') + cast(ID as varchar) + ','
from
(select top 100 percent
ID
from
foo
order by
newid()) a
select @c

SQL 2K5 treats top differently and I don't remember how ... but in SQL 2K5, the IDs come back ordered. I'm still playing with it ...

Jay White

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-27 : 09:15:29

Odd ... Top 100 percent brings the list back ordered ... Top 99 percent randomizes ... (in 2K5)

Jay White

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-27 : 09:18:37

Cool ... this works in 2K5


declare @c varchar(8000)
select top 8001
    @c = coalesce(@c + ',', '') + cast(ID as varchar)
from
    foo
order by
    newid()
select @c

Jay White

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources