SELECT returns multiple answers

Question

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?

Possibly answered by https://stackoverflow.com/questions/8674718/best-way-to-select-random-rows-postgresql — High Performance Mark, Sep 13 '22 at 09:08

score 1 · Answer 1 · answered Sep 13 '22 at 09:12

1

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);

answered Sep 13 '22 at 09:12

Laurenz Albe

209,280
17
206
263

Ok, but this select is a part of other select with generate_series, so it takes one name and uses only it – dobriy denyok Sep 13 '22 at 15:02
That's a somewhat cryptic comment. I can only answer the question as it was asked, you will have to apply that to your real problem. – Laurenz Albe Sep 13 '22 at 15:07
ok, edited question for better undestanding – dobriy denyok Sep 13 '22 at 16:28
Thanks. So where is the problem with writing two subqueries? – Laurenz Albe Sep 13 '22 at 16:32

YJR · Accepted Answer · 2022-09-13T17:21:46.853

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;

SELECT returns multiple answers

2 Answers2