select a random set of rows

Question

Hello,

I would like to select a random set of rows from one of the surveys. ADQL supports the RAND function and the SQL query below works on the Vizier ADQL TAP interface here: http://tapvizier.u-strasbg.fr/adql/

SELECT TOP 100 *
FROM "I/261/fonac" WHERE RAND() < 0.1

When I try this at https://datalab.noirlab.edu/query.php

SELECT TOP 10 * FROM ls_dr6.tractor_primary WHERE RAND() <0.01

I get the error:

Error: IllegalArgumentException: Function [rand] is not found in TapSchema

Answer 1

Hi, thanks for reaching out.

On most object tables we have a column named "random_id" which contains random floats between 0 and 100. You can thus select a random sample by giving any range for random_id. Example:

select * from ls_dr6.tractor_primary where random_id between 20.0 and 20.1

This would return a random set comprising 0.1 percent of the entire table. A previous "select count(*) from ls_dr6.tractor_primary" will tell you the total number of rows, so you can adjust your random_id range accordingly. 

To select a different random sample, simply choose different limits for random_id.

Best regards,

Robert