Netezza Select Random Rows and Example

If you are working on data warehouse or any database query then you might have received the request to get random numbers based on on some key columns. In this article, we will check Netezza select random rows in nzsql and explanation with an examples.

This article also explains you on Netezza select random samples that you may use in other client related applications.

Netezza Select Random Rows

To demonstrate the Netezza select random, we will use the Netezza random() built in function.

Netezza Select Random Rows Example 

Suppose you have student with ID and subject codes, and if any one ask you to choose random subjects for each students you can follow the instructions given in this article.

Below is the steps and queries for Netezza select random rows from the tables:

create table
stud_dtls(stud_id int,
Sub_cod int)
distribute on random;

Insert records to the test tables for demonstration:

insert into stud_dtls values(1,1001);
insert into stud_dtls values(1,1002);
insert into stud_dtls values(2,1002);
insert into stud_dtls values(3,1003);
insert into stud_dtls values(4,1011);
insert into stud_dtls values(4,1002);
insert into stud_dtls values(4,1014);
insert into stud_dtls values(5,1016);
insert into stud_dtls values(5,1023);

Below is the query that can be used to select random rows in Netezza. This query uses Netezza built-in random function.

SELECT stud_id
 ,max(Sub_cod_value) AS Sub_cod
FROM (
 SELECT stud_id
 ,first_value(Sub_cod) OVER (PARTITION BY stud_id ORDER BY random() ) AS Sub_cod_value
 FROM stud_dtls
 ) a
GROUP BY stud_id
ORDER BY 1;

The above query will give different subject code for every different run.

Check:

• Quick and best way to Compare Two Tables in SQL
• Netezza Pivot  Rows to Column with Example
• Netezza Split Delimited Fields into Table Records and Examples
• Netezza Update Join Syntax and Examples
• Count Records from all Tables in Database and Example