MySql - Get Min, Max, and 3 random Rows

Question

I want a MySql query to get 5 rows which include min price row, max price row and other 3 random row.

Table:

ID  Product Price
1   data    2
2   data    20
3   data    55
4   data    24
5   data    2
6   data    15
7   data    10
8   data    33
9   data    3
10  data    30

Expected Result(with 3 random rows)

ID  Product Price
1   data    2
3   data    55
4   data    24
6   data    15
7   data    10

Any help would be greatly appreciated!

random select refer to[MySQL select 10 random rows from 600K rows fast](https://stackoverflow.com/q/4329396/6521116) ; max select refer to[SQL Select only rows with Max Value on a Column](https://stackoverflow.com/q/7745609/6521116); min select refer to[MYSQL how to select data where a field has a min value](https://stackoverflow.com/q/13357144/6521116) — LF00, Jun 17 '17 at 11:31

score 10 · Accepted Answer · answered Jun 17 '17 at 14:07

10

SELECT table.*
  FROM table
     , ( SELECT @minPrice := ( SELECT min(Price) FROM table ) minPrice
              , @minId    := ( SELECT id FROM table WHERE Price = @minPrice ORDER BY rand() LIMIT 1 )
              , @maxPrice := ( SELECT max(Price) FROM table ) maxPrice
              , @maxId    := ( SELECT id FROM table WHERE Price = @maxPrice ORDER BY rand() LIMIT 1 )
       ) tmp
  WHERE table.id in (@minId,@maxId)
UNION
(SELECT *
   FROM table
   WHERE Price not in (@minPrice,@maxPrice)
   ORDER BY rand()
   LIMIT 3
)

answered Jun 17 '17 at 14:07

Sal

1,307
1
8
16

Hi, I have one more condition to be included, that is, The table have one more column product ID, I need to take 5 products(min max and 3 random) from each product ID. Could you please help? – keerthi Jun 19 '17 at 04:12
Query would be different depending on desired output. However, as @Strawberry suggested, you can LOOP by product ID and simply filter by product ID in all WHERE clauses where table (prices) is used. – Sal Jun 19 '17 at 16:21

score 6 · Answer 2 · edited Jun 17 '17 at 11:28

6

You can do this like ,

select * from table order by Price asc limit 0,1
union all
select * from table order by Price desc limit 0,1 
union all
select * from table order by RAND()  limit 0,3

edited Jun 17 '17 at 11:28

LF00

27,015
29
156
295

answered Jun 17 '17 at 11:25

Vibha Chosla

733
5
12

Surely UNION DISTINCT !! – Strawberry Jun 17 '17 at 11:31
1

...but that would still leave you with a problem – Strawberry Jun 17 '17 at 11:32
2

You results may less than 5 items. and by rand is slowly when the table is too big. – LF00 Jun 17 '17 at 11:33
It will a provability where other there may include max or min price – Bhunesh Satpada Jun 24 '17 at 09:35

score 2 · Answer 3 · answered Jun 24 '17 at 10:27

you can get it with UNION and sub-query:

(SELECT * FROM table ORDER BY Price ASC LIMIT 0 , 1 )
UNION ALL 
(SELECT * FROM table ORDER BY Price DESC limit 0,1 )
UNION ALL 
(SELECT * FROM table WHERE Price NOT IN ( SELECT CONCAT( MIN(  `Price` ) ,  ',', MAX(  `Price` ) ) AS MaxPrice FROM table ) ) ORDER BY RAND( ) LIMIT 0 , 3 )

Keith John Hutchison · Answer 4 · 2017-06-17T13:02:19.490

So ... get the min, get the max, get all the other records that are not min and max, sort by rand and return the first 3 that are not min and max.

Here is the SQL fiddle

-- get the first occurence of any item matching the products and prices returned
select min(top_bottom_and_3_random.id) id, top_bottom_and_3_random.product, top_bottom_and_3_random.price from (
    -- get the min and the max
    select distinct product, price from top_bottom_and_3_random where price in (
        select max( price) from top_bottom_and_3_random
        union select min( price ) from top_bottom_and_3_random
    ) union 
    select product, price from ( 
        -- get 3 random rows that are not max or min
        select rand() rand, product, price from (
            select product, price from top_bottom_and_3_random where price not in (
                select max( price) from top_bottom_and_3_random
                union select min( price ) from top_bottom_and_3_random
            ) group by product, price
        ) rand_product_price_group
         order by rand
         limit 3
    ) random_mix
) min_max_3_random
inner join top_bottom_and_3_random
on min_max_3_random.product = top_bottom_and_3_random.product
and min_max_3_random.price = top_bottom_and_3_random.price
group by top_bottom_and_3_random.product, top_bottom_and_3_random.price
order by id

-- example results
id  product price
1   data    2
3   data    55
4   data    24
7   data    10
10  data    30

Strawberry · Answer 5 · 2017-06-17T12:42:14.460

1

SELECT x.*
  FROM my_table x 
  JOIN (SELECT MIN(price) a, MAX(price) b FROM my_table) y 
 ORDER 
    BY COALESCE(x.price NOT IN (a,b))
     , RAND() 
 LIMIT 5;

To address Keith's concerns... so, if we should always have 3, and either 1 or 5...

SELECT x.id
     , x.product
     , x.price
  FROM my_table x 
  JOIN (
        (SELECT id FROM my_table ORDER BY price, RAND() LIMIT 1)
        UNION
        (SELECT id FROM my_table ORDER BY price DESC, RAND() LIMIT 1)
       ) y
   GROUP
      BY x.id
       , x.product
       , x.price
 ORDER 
    BY MIN(COALESCE(x.id != y.id)) 
     , RAND()
 LIMIT 5;

...but this is starting to be a bit of a mouthful - it may be smarter to solve this in application code.

edited Jun 17 '17 at 12:42

answered Jun 17 '17 at 11:39

Strawberry

33,750
13
40
57

Nice ... and this resulted in one set with min - 2 duplicated on my device. { "data": [ { "id": 5, "Product": "data", "Price": 2 }, { "id": 1, "Product": "data", "Price": 2 }, { "id": 3, "Product": "data", "Price": 55 }, { "id": 10, "Product": "data", "Price": 30 }, { "id": 8, "Product": "data", "Price": 33 } ] } – Keith John Hutchison Jun 17 '17 at 12:11
The way I read the question ... it's min, max and 3 random in between. – Keith John Hutchison Jun 17 '17 at 12:19
min, max _and_ 3 other. – Keith John Hutchison Jun 17 '17 at 12:23
anyway .. your code example is cleaner than mine ... thanks. – Keith John Hutchison Jun 17 '17 at 12:24
Still got a set with duplicates ... I think the randoms have to have the min and max excluded. { "data": [ { "id": 3, "Product": "data", "Price": 55 }, { "id": 1, "Product": "data", "Price": 2 }, { "id": 2, "Product": "data", "Price": 20 }, { "id": 8, "Product": "data", "Price": 33 }, { "id": 1, "Product": "data", "Price": 2 } ] } – Keith John Hutchison Jun 17 '17 at 12:36
@KeithJohnHutchison Again, see edit. Note that comments can be deleted. – Strawberry Jun 17 '17 at 12:43
I know comments can be deleted ... My answer always returns distinct answers. Only Issue I've found is it occasionally returns 4 instead of 5 answers. I'm looking at that. – Keith John Hutchison Jun 17 '17 at 12:54
@KeithJohnHutchison I believe mine always returns 5 distinct rows – Strawberry Jun 17 '17 at 12:58
Some does mine now .. and your code example is more elegant than mine. Cheers and thanks. – Keith John Hutchison Jun 17 '17 at 13:06

score 1 · Answer 6 · answered Jun 20 '17 at 11:55

1

You can take help of MySQL sub-query to get the desired result

select * from table WHERE Price = (SELECT MIN(Price ) FROM table)
union all
select * from table WHERE Price = (SELECT MAX(Price ) FROM table)
union all
select * from table order by RAND()  limit 0,3

answered Jun 20 '17 at 11:55

RAUSHAN KUMAR

5,846
4
34
70

What happens if there is more than 1 row with a minimum or maximum price? – Keith John Hutchison Jun 24 '17 at 00:18

score 0 · Answer 7 · answered Jun 17 '17 at 11:34

0

(select * from  table order by Price limit 1) 
 union 
(select * from table  order by Price desc limit 4)

answered Jun 17 '17 at 11:34

Mattia Dinosaur

891
10
29

Not exactly random is it - but, oddly, it may be on exactly the right lines! – Strawberry Jun 17 '17 at 11:43

MySql - Get Min, Max, and 3 random Rows

7 Answers7