Question 335382 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql: Selecting distinct row and showing the selected id. details will show below

i have problem on using group by and distinct. when using group by, id cant be shown. but if usingn distinct. it will shown all id and product id.
i want to use distinct product id and id will shown one of it below using random.
select id,distinct(product id),cost from price order by rand()

This will be the data.

id product id price
1 1 3
2 1 4
3 1 3
4 2 6
5 3 4
6 3 3
7 3 1

After query will shown this.

id product id price
2 1 4
4 2 6
5 3 4
Tags:

2

Answers


  1. 0 
    WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY RAND()) rn
    FROM tablename
)
SELECT id, product_id, price
FROM cte
WHERE rn = 1;

    ?

    Login or Signup to reply.
  2. 0 
    SELECT
  (SELECT id FROM price WHERE product_id = p.product_id ORDER BY RAND() LIMIT 1) AS id,
  p.product_id,
  (SELECT price FROM price WHERE product_id = p.product_id ORDER BY RAND() LIMIT 1) AS price
FROM
  (SELECT DISTINCT product_id FROM price) p;

    Demo: https://dbfiddle.uk/rZY6AA-j

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search