1

How to convert row into column with random with mysql? Distinct (image_url)

 select p.email , 
        image_url AS image_url_1,
        image_url AS image_url_2 ,
        image_url  AS image_url_3,
        image_url  AS image_url_4
    FROM
        poll p
    GROUP BY
        email
    ORDER by RAND()

Also, in this query

select email,
    (select IFNULL((image_url from poll p1 order by rand() limit 1), NULL) as image_1,
    (select IFNULL((image_url from poll p2 order by rand() limit 1), NULL) as image_2,
    (select IFNULL((image_url from poll p3 order by rand() limit 1), NULL) as image_3,
    (select IFNULL((image_url from poll p4 order by rand() limit 1), NULL) as image_4
from poll
group by email

How to display null if there is less than 4 image_url and how to make it distinct?

user1615767
  • 11
  • 5

1 Answers1

0
select email,
(select image_url from poll p1 order by rand() limit 1) as image_1,
(select image_url from poll p2 order by rand() limit 1) as image_2,
(select image_url from poll p3 order by rand() limit 1) as image_3,
(select image_url from poll p4 order by rand() limit 1) as image_4
from poll
Alex Monthy
  • 1,827
  • 1
  • 14
  • 24
  • 1
    Expensive; could end up with the same image multiple times; and shouldn't each subquery filter on `p.email`? – eggyal Sep 10 '12 at 09:56
  • Maybe it's expensive, but I don't assume that such a query is posed to a table of more than a few thousand rows. And yes, the email filter should/could be added. But as long as we don't know what the OP is aiming at... – Alex Monthy Sep 10 '12 at 15:05
  • select email, (select IFNULL((image_url from poll p1 order by rand() limit 1), NULL) as image_1, (select image_url from poll p2 order by rand() limit 1) as image_2, (select image_url from poll p3 order by rand() limit 1) as image_3, (select image_url from poll p4 order by rand() limit 1) as image_4 from poll group by email How could i display NULL if there is less than 4 image_url? – user1615767 Sep 11 '12 at 01:01