CODEWITHSUNDEEP
sqlteradata
user650654
user650654

Reputation: 6128

Teradata: Keep a random single row per column value

Suppose I have a two-column table (t1) with the following rows:

id   animal
---- ------
1    dog
1    pig
1    donkey
2    cow
2    horse
2    dog
2    donkey

Now, I want to retain only one row for a given id. I could do a minimum or maximum along with a group-by:

create table t2 as (
    select id, min(animal)
    from t1
    group by id
) with data unique primary index(id);

Is there a way to get a random row for each id? Something that is less predictable than a min or max.

Upvotes: 1

Views: 2385

Answers (1)

dnoeth
dnoeth

Reputation: 60482

select id, animal
from t1
qualify row_number() over (partition by id order by 1) = 1

This is not real random, to get a truly random result you need:

select id, animal
   ,rnd -- without rnd the optimizer removes the Derived Table and throws an error:
        -- [5521] The RANDOM function can not be used in Aggregates or Ordered Analytical Functions.
from 
 ( select id, animal,
      random(1,100) as rnd
   from t1
  ) as dt
qualify row_number() over (partition by id order by rnd) = 1

Upvotes: 1

Related Questions