SELECT returns multiple answers-CodePudding

I have names table with columns id, name and last_name. So what i'm trying to do is select random name and last_name from it, i tried this:

SELECT (SELECT name FROM names WHERE ID =floor(random()* 18   1   x - x)),
       (SELECT last_name FROM names WHERE ID = floor(random()* 18   1   x - x))
FROM GENERATE_SERIES(1, 100) as x;

But it says it returns more than one row, why and how can i fix it?

CodePudding user response：

The volatile random() function will generate a different value for each row of your table, so none or more than one row could be selected. You can avoid that by putting random() into an uncorrelated subquery, then it will only be executed once:

SELECT name FROM names
WHERE id = floor((SELECT random())*18   1);

CodePudding user response：

When you use your query it will generate random id for each row. Therefore it get match with multiple rows in name table. Therefore it return multiple rows for this part SELECT name FROM names WHERE ID =floor(random()* 18 1 x - x)). SQL not allow more than one row to get select.

This will work. I think this is what you want.

SELECT (SELECT name FROM names WHERE ID =(select floor(random()* 18   1   x - x))) ,
           (SELECT last_name FROM scientist WHERE id = (select floor(random()* 18   1   x - x))) FROM GENERATE_SERIES(1, 100) as x;