CODEWITHSUNDEEP
mysql
Deden Sembada
Deden Sembada

Reputation: 31

Select in MySQL with priority

I've table data looks like this:

SQL

How do in SQL if I want to select data with limit 5 and show by priority type=image as much as 70% and type=text as much as 30%?

Upvotes: 2

Views: 633

Answers (2)

Ashish Detroja
Ashish Detroja

Reputation: 1082

Please try this query :

(SELECT * FROM `tablename` where `type` = 'text' limit 0,2) UNION (SELECT * FROM `tablename` where `type` = 'image' limit 0,5)

It return something like this ,

+--------+-------+------------+
| id     |  type | value      |
+--------+-------+------------+
|   1    | text  | text1      |
|   2    | text  | text2      |
|   4    | image | image1.jpg |
|   5    | image | image2.jpg |
|   6    | image | image3.jpg |
|   7    | image | image4.jpg |
|   8    | image | image5.jpg |
+--------+--------------------+

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

To do this in one query will require running totals and will be fairly complex. A nicer solution might be to do two separate queries and then aggregate the results set together using a UNION:

SELECT * FROM yourTable
WHERE type='text'
ORDER BY RAND()
LIMIT 3
UNION ALL
SELECT * FROM yourTable
WHERE type='image'
ORDER BY RAND()
LIMIT 7

I am returning 10 rows because 30% and 70% of 5 does not give a whole number. I am also eschewing the case where there might be fewer than 3 or 7 records for types of text and image, respectively, which would throw off your desired ratio.

Upvotes: 1

Related Questions