Selecting a Random Subset in SQL (Sybase Server IQ)

Question

I'm trying to select a random subset of records using either rand() or newid(), but any time I run the following, I always get the same results back:

SELECT *,rand() as rid
INTO #mytable
FROM othertable

select top 10 * from #mytable order by rid

I've tried every variation of this, but it seems that sybase server iq always returns the same rand() value when I do it like the above. When I try to do

SELECT * FROM othertable order by newid()

I get an error saying it's illegal to use newid() in the order by.

Any suggestions?

Answer 1

In Sybase T-SQL, The NEWID() system function generates human-readable, globally unique IDs. Include the NEWID() system function in the ORDER BY clause when issuing the SELECT statement on the target table.

Usage:

SELECT top 10 id from TABLE order by NEWID();