Re: random records wanted

There is a DBMS_RANDOM built-in package available in version 8. I haven't used it yet, but it looks simple to use. It's easy to find in the Oracle documentation, or O'Reilly's "Oracle Built-in Packages" book (excellent book by the way). The only way I can think to do it 'loosely' using SQL is to create a proc that puts a list of x number of random numbers (no dups) into a temp table. Then you can use the following steps:

1. Execute your proc that generates random numbers, passing it the number of random numbers to generate (60 in your case).
2. Then you can execute the following SQL: SELECT TITLE
   , CATEGORIE
   FROM ( SELECT ROWNUM , TITLE , CATEGORIE FROM SONG WHERE CATEGORIE = 'Gansta Rap' ) Song
   , (
   SELECT ROUND(RANDOMLIST.RANDOMNUMBER/99999999)*Limit.Upper,0) RandomNumber FROM RANDOMLIST , ( SELECT COUNT(TITLE) Upper FROM SONG WHERE CATEGORIE = 'Gansta Rap' ) Limit ) Randomlist WHERE Song.ROWNUM = Randomlist.RandomNumber;

That 'should' work, but it's not too pretty having to remember to execute that logic before each execution of the SQL. Alternatively, you could create a proc with the following logic:

1. Get a count to determine a range for your random numbers (1 to n): SELECT COUNT(TITLE) INTO nUpperLimit FROM SONG WHERE CATEGORIE = 'Gansta Rap';
2. Create an array and fill it with 60 random numbers between 1 and nUpperLimit, NO DUPS. DBMS_RANDOM will return 8 digit integers, so perform some arithmetic to get it into your range: nRandomNumber := ROUND((DBMS_RANDOM.RANDOM/99999999)*nUpperLimit,0);
3. Use a cursor for: SELECT ROWNUM
   , TITLE
   , CATEGORIE
   FROM SONG WHERE CATEGORIE = 'Gansta Rap'.
4. Loop through the cursor record by record checking the ROWNUM against the list in your array. Perform whatever action you desire on those that match (DBMS_OUTPUT, UTL_FILE, INSERT INTO temp_table, etc.) and do nothing for those that don't.

Just some thoughts, as I've never used it before. I can think of a ton on ways to do it programmatically, but none in pure SQL. Hope that gives you some ideas...

Michael J. Ort

In article <38F1DA23.30EEB6D7_at_mediaaktiv.de>,   Stefan <stefanp_at_mediaaktiv.de> wrote:

> Hi,
>
> got a song - database like
>
> titel           NUMBER
> categorie  VARCHAR(2)
>
> just need about 60 songs of a specific categorie RANDOMLY
>
> how to do that ?
> any workarounds ?
>
> thanx
> stefan
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Apr 10 2000 - 00:00:00 CDT