MYSQL pickup Random row after order by

Question

I want to pick a random row after doing an order by.

My result can sometime return 2 rows with same value. So what i want to do it that i want to select 1 row randomtly between the same rows returned.

In the below example i want to return 1 row between the 2 same rows returned.

Thanks.

select nondox, account, id 
    from rates2 where weight='32' and country_code='US' 
         and service like '%INT%' 
    order by nondox,account

Result of the above query is

nondox  account   id 
276.16  610661731 25805209
276.16  610798714 2108989
391.68  610662766 1281799

Why not just using `ORDER BY RAND()....LIMIT 1` in your sql? — B001ᛦ, May 26 '20 at 11:01
See this [answer](https://stackoverflow.com/a/19419/10732434). It's for more database systems but [tag:mysql] is listed first. — sanitizedUser, May 26 '20 at 17:25
Does this answer your question? [How to request a random row in SQL?](https://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql) — sanitizedUser, May 26 '20 at 17:25
ORDER BY RAND()....LIMIT 1 will not select the cheapest nondox value — Beenish Yousafzai, May 28 '20 at 08:12

score 0 · Answer 1 · answered May 26 '20 at 11:11

0

Can provide more details ?
This will eliminate the similar nondox values it's not completely random but it should work. select account, id , nondox from rates2 where weight='32' and country_code='US' and service like '%INT%' group by nondox order by nondox,account

answered May 26 '20 at 11:11

anouar es-sayid

355
5
11

your code will always select the 2nd row. I want to cycle between 1st and 2nd rows – Beenish Yousafzai May 28 '20 at 08:14

Astronaute · Answer 2 · 2020-05-26T12:11:45.067

0

you can use function RAND() that generates a random value for each row in the table

SELECT * FROM table_name
ORDER BY RAND()
LIMIT 1;

for more informations checkout this link

edited May 26 '20 at 12:11

answered May 26 '20 at 11:12

Astronaute

219
3
19

If I order by rand() then how can I make sure the cheapest nondox value is at the top? – Beenish Yousafzai May 28 '20 at 08:07
you can use Min function min(nondox) – Astronaute May 28 '20 at 13:49

score -2 · Answer 3 · answered May 26 '20 at 11:03

-2

you can put the result in an array, then create a random integer with random(with limit) the output the values of the index from random. example random outputs 2, you return array[random+1] ie array[3]

answered May 26 '20 at 11:03

wanjaswilly

314
2
11

MYSQL pickup Random row after order by

3 Answers3