How to get the two random unique advertisement based on its type?

Question

In MySQL I have one table called advertisement which contains different type of advertisements like banner and square.

I want exact two random advertisement with different advertisement type.

SELECT DISTINCT id, ad_type
FROM tbl_advertisement
WHERE ad_type IN ('banner', 'square')
ORDER BY RAND()
LIMIT 2

It gives me sometime unique ad_type but sometimes not as expected.

  [
    RowDataPacket { id: 9, ad_type: 'square' },
    RowDataPacket { id: 8, ad_type: 'square' }
  ]

Expected output any two random advertisement with unique ad_type:

  [
    RowDataPacket { id: 3, ad_type: 'square' },
    RowDataPacket { id: 4, ad_type: 'banner' }
  ]

Answer 1

One option uses window functions, available in MySQL 8.0:

select id, ad_type
from (
    select ad.*, row_number() over(partition by ad_type order by rand()) rn
    from tbl_advertisement ad
    where ad_type in ('banner','square') 
) t
where rn = 1

In ealier versions, the simplest option probably is union all:

(select id, ad_type from tbl_advertisement where ad_type = 'banner' order by rand() limit 1)
union all
(select id, ad_type from tbl_advertisement where ad_type = 'square' order by rand() limit 1)