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!
8 Answers
Answers 1
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 ) Answers 2
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 Answers 3
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.
-- 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 Answers 4
(select * from table order by Price limit 1) union (select * from table order by Price desc limit 4) Answers 5
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.
Answers 6
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 Answers 7
This question may seem simple at first but there are a few possible gotchas:
Gotcha 1: All rows must be distinct. In particular, the 3 random rows shouldn't include the rows with the maximum or minimum price (since otherwise there could either be duplicate rows or less than 5 rows returned). UPDATE - Gotcha 1a: What if there are less than 5 rows in the table? Perhaps an edge case but presumably then all the rows should be returned exactly once.
Gotcha 2: Using ORDER BY in any of the subqueries could be very slow if there are a lot of rows.
The solution below attempts to get around these gotchas by picking single rows by ID and excluding the IDs that have already been picked when selecting random rows. (The random picking SQL is non-trivial but the complexity is necessary to overcome "Gotcha 2".)
Demo
http://rextester.com/OGLS84660
SQL
SELECT * FROM tbl WHERE ID IN ((SELECT @id1 := ID FROM tbl WHERE Price = (SELECT MIN(Price) FROM tbl) LIMIT 1), (SELECT @id2 := ID FROM tbl WHERE ID <> @id1 AND Price = (SELECT MAX(Price) FROM tbl) LIMIT 1), (SELECT @id3 := ID FROM (SELECT ID, (SELECT COUNT(*) AS cnt FROM tbl t2 WHERE t2.ID <= t1.ID AND t2.ID NOT IN (@id1, @id2)) AS cnt FROM tbl t1 WHERE t1.ID NOT IN (@id1, @id2)) t3 WHERE t3.cnt = (SELECT rnd FROM (SELECT ID, CEILING(RAND() * ((SELECT MAX(ID) FROM tbl) - (SELECT MIN(ID) FROM tbl) - 1)) AS rnd FROM tbl LIMIT 1) t4)), (SELECT @id4 := ID FROM (SELECT ID, (SELECT COUNT(*) AS cnt FROM tbl t2 WHERE t2.ID <= t1.ID AND t2.ID NOT IN (@id1, @id2, @id3)) AS cnt FROM tbl t1 WHERE t1.ID NOT IN (@id1, @id2, @id3)) t3 WHERE t3.cnt = (SELECT rnd FROM (SELECT ID, CEILING(RAND() * ((SELECT MAX(ID) FROM tbl) - (SELECT MIN(ID) FROM tbl) - 2)) AS rnd FROM tbl LIMIT 1) t4)), (SELECT ID FROM (SELECT ID, (SELECT COUNT(*) AS cnt FROM tbl t2 WHERE t2.ID <= t1.ID AND t2.ID NOT IN (@id1, @id2, @id3, @id4)) AS cnt FROM tbl t1 WHERE t1.ID NOT IN (@id1, @id2, @id3, @id4)) t3 WHERE t3.cnt = (SELECT rnd FROM (SELECT ID, CEILING(RAND() * ((SELECT MAX(ID) FROM tbl) - (SELECT MIN(ID) FROM tbl) - 3)) AS rnd FROM tbl LIMIT 1) t4))); Answers 8
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 ) 
0 comments:
Post a Comment