Krioma.net

Time for another piece of SQL code. This might, like the [previous one], be useful to someone. What this one does it from a database in the SDSS database, with objID and plate in, returns them in a random order. Now why would you want to do this? Well, maybe, you just want to return a random sample from the SDSS without having to download the sample (due to its size it is unfeasible to download it all) and run some C program. This allows the randomization to be completed without having to download any data. The idea for this came from [SqlTeam.com].

-- don't bother with useless row counts

set nocount on

declare @rand_temp float

CREATE TABLE #temp (objID bigint NOT NULL, Ran_Num float NULL, plate int NOT NULL)

INSERT #temp (objID, plate) SELECT objID, plate FROM z_table

--assign a new random value to each key value in #temp

DECLARE Random_Return CURSOR

FOR SELECT Ran_Num FROM #temp

OPEN Random_Return

FETCH NEXT FROM Random_Return

into @rand_temp

WHILE @@Fetch_Status != -1

BEGIN

UPDATE #temp SET Ran_Num = rand()

WHERE CURRENT OF Random_Return

FETCH NEXT FROM Random_Return

into @rand_temp

END

CLOSE Random_Return

DEALLOCATE Random_Return

--export the data from the temp table

select *

INTO z_random

from #temp

order by Ran_Num