CODEWITHSUNDEEP
sqloracle-database
randombee
randombee

Reputation: 719

Multiple rows returned trying to select specific row in Oracle SQL

I am trying to return a value from field tid in a specific row in Oracle SQL but I am getting a weird behavior.

First, I tried this:

 select tid from
 (select tid, rownum as rn from
      (select tid from Train))
 where rn=5;

and the value in row 5 is successfully returned. However, when I try to randomize the value to take the row with rownum from 1 to 15 by doing the following:

 select tid from
 (select tid, rownum as rn from
      (select tid from Train))
 where rn=round(dbms_random.value(1,15));

more rows are returned, not just one. What am I doing wrong?

Upvotes: 0

Views: 99

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

That is because dbms_random() is called for each row, so it can generate duplicate results. Instead, if you want one random row, try something like this:

select tid
from (select tid,
             row_number() over (order by dbms_random.value()) as seqnum
      from Train
     ) t
where seqnum = 1;

This will always return the row with "seqnum = 1", but it will be a random tid.

Upvotes: 1

Related Questions