MySQL: using random function in existing table for generating derived column

Question

i have a table 'product' with column 'category' which consists of 10 different categories without a column category_id. i want to derive a table with new column category_id for each category.

select product_id,description,category, round(rand(10)) as category_id from product;
select product_id,description,category, rand( over partition by 10) from product;

using above query i have tried "round(rand())" however this gives only 0 and 1, but i want it to allocate category_id for 10 products from 1 to 10.

Also i want to retrieve the products from two random category

nbk · Accepted Answer · 2020-05-28 16:32:10Z

1

Use FLOOR(1+ rand() * 10) this will give you a number between 1 and 10

select product_id,description,category,FLOOR(1+ rand() * 10) as category_id from product;

answered May 28, 2020 at 16:32

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

5 Comments

Harris Hilton Over a year ago

Thanks for quick update, still it dont creates unique number, i can see same number repeated. select product_id,description,category,FLOOR(1+ rand() * 10) as category_id from product group by category; Also under each category i have many products. Goal is to retrieve group of products listed in any random category

nbk Over a year ago

show some data and also wanted result, the code above generates random numbers between 1 to 10. random also includes that numbers will repeat.

Harris Hilton Over a year ago

used query: select product_id,description,category, FLOOR(1+ rand() * 10) as category_id from product group by category; output: product_id, Description, category, category_id 1 Biscuits Baked goods 4 11 Coke Beverages 1 18 Ariel Cleaning products 3 25 Barbecue Sauce Condiments / Sauces 2 expected output is as below: product_id, description, category, category_id 1 Biscuits Baked goods c1 11 Coke Beverages c2 18 Ariel Cleaning products c3 25 Barbecue Sauce Condiments / Sauces c4

nbk Over a year ago

please edit your post and add not only result but also where the resulr comes from

Harris Hilton Over a year ago

Thanks , i got the correct query, below query works as per my expectations, i work using MySQL workbench. solution:

select p.product_id, p.description,p.category from product p  join (select c.product_id,c.category from product c order by rand() limit 2) cc on cc.category=p.category;

Collectives™ on Stack Overflow

MySQL: using random function in existing table for generating derived column

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related