3

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?

Danny Beckett
  • 20,529
  • 24
  • 107
  • 134
user1167650
  • 3,177
  • 11
  • 34
  • 46
  • You'll want to see the random number generator before making calls to rand(). I suggest you put it into a stored proc; you can then seed the random number generator and then execute the first query. `ORDER BY NEWID()` is peculiar to MS SQL Server so it would not work in Sybase. The following thread might be helpful: http://database.ittoolbox.com/groups/technical-functional/sybase-l/using-the-function-rand-on-sybase-iq-1547367 – David Faber Mar 20 '12 at 18:29
  • I think you mean "__seed__ the random number generator"... – Jonathan B Mar 21 '12 at 09:52

3 Answers3

3

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();
user1126070
  • 5,059
  • 1
  • 16
  • 15
0

You can get random effect by using row_number() function and current time values. Try something like this:

with q as (
    SELECT
        *,
        row_number() over (order by cPublisher) n, -- getting row number
        DATEPART(ms, now()) t -- getting current ms value
    FROM 
        #mytable
)

select top 10
    *
from
    q
order by 
    -- order by some combination t and n, for example t * n and sort it like char
    cast(t * n as char)
Vadim Zin4uk
  • 1,716
  • 22
  • 18
  • I'm trying to understand why this would give a random order. It seems that `t * n` could easily be monotonically increasing. Maybe you need some kind of random hash of `t` and `n` instead of just `t * n`. – LarsH Sep 06 '19 at 20:21
0

Random values in the IQ engine are deterministic, force a query through the ASA engine like below (Warning: it is slow if you have many rows).

select (select rand() as rnd) from mytable
Rafał Rawicki
  • 22,324
  • 5
  • 59
  • 79
Roland
  • 1