Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I select random rows in MySQL?

Tags:

mysql

mytable

pid name field
=== ==== =====
1    A1   0
2    A2   1
3    A3   1
4    A4   0   
5    A5   0

This is my table structure. Here I want to select randomly 4 rows so I use RAND() mysql function in my query my questions is

How do I pair to rows. I mean, I wanna select pid 2 and 3 always one ofter another. I need in bellow order. i don't want to break the pair A2 A3

A1 A2 A3 A4 or A2 A3 A4 A1 or A2 A3 A4 A5 or A4 A5 A2 A3 and etc

I used the query below but it's not working for me

SELECT * FROM mytable ORDER BY RAND() ASC limit 0,4
like image 591
Gowri Avatar asked Feb 24 '11 11:02

Gowri


1 Answers

turbod was close with his answer, he was just ordering randomly, when it seems you wanted to order by pid, after getting the random rows you wanted in conjunction with the ones concerning A2 and A3:

(
    SELECT *
    FROM `mytable`
    WHERE 
        name ='A2' OR 
        name ='A3'
    LIMIT 2
)
UNION
(
    SELECT DISTINCT *
    FROM `mytable`
    WHERE 
        name !='A2' OR 
        name !='A3'
    ORDER BY RAND( ) LIMIT 2
) 
ORDER BY `pid`
like image 81
Mike S Avatar answered Oct 11 '22 02:10

Mike S