randombee randombee - 3 months ago 8
SQL Question

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?

Answer

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.

Comments