Postgresql: Random value for a column selected frm second table

Question

I have two tables drivers and drivers_names. What I want is for every driver I select from the first table to have a random name from the second, but what I get is one name for all drivers in the result. Yes, it is different every time but is one for all. Here is my query, I'm using postgresql.

SELECT
    drivers.driver_id AS drivers_driver_id,
    (
        SELECT
            drivers_names.name_en
        FROM
            drivers_names
        ORDER BY random() LIMIT 1
    ) AS driver_name
FROM
    drivers

Result:

11  Denis
13  Denis
7   Denis

Tables structure.

drivers

+--------------+
| column_name  |
+--------------+
| driver_id    |
| property_1   |
| property_2   |
| property_3   |
+--------------+

drivers_names

+-------------+
| column_name |
+-------------+
| name_id     |
| name_en     |
+-------------+

if you will show structure of both the tables it will be helpful. — Nagri
– Nagri, Commented Aug 1, 2012 at 6:53

Daniel Vérité · Accepted Answer · 2012-08-01 11:18:37Z

3

Postgres probably evaluates the subselect only once because technically there's no reason to evaluate it for every row. You could force it by referencing a column from the drivers table into the subselect, like this:

SELECT
    drivers.driver_id AS drivers_driver_id,
    (
        SELECT
            drivers_names.name_en
        FROM
            drivers_names
        ORDER BY random()+drivers.driver_id LIMIT 1
    ) AS driver_name
FROM
    drivers

answered Aug 1, 2012 at 11:18

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

arod Over a year ago

you can also put random()+drivers.oid::integer which may be a more general solution to all cases (my id was alphanumeric)

Collectives™ on Stack Overflow

Postgresql: Random value for a column selected frm second table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related