ORDER BY RAND() in MySQL

I want use ORDER BY RAND() query mysql. But I have some question want to ask.

my mysql table 'image' like:

id | image | width | heig开发者_运维百科ht |
1  | 1.jpg | 640   | 480    |
2  | 2.jpg | 800   | 600    |
3  | 3.jpg | 480   | 600    |
4  | 4.jpg | 720   | 480    |
5  | 5.jpg | 600   | 800    |
6  | 6.jpg | 1024  | 768    |
7  | 7.jpg | 768   | 1024   |
8  | 8.jpg | 800   | 600    |
9  | 9.jpg | 720   | 560    |
10 | 10.jpg| 800   | 600    |

I need do a mysql query ORDER BY RAND() print 5 images, the orders are:

first: width >= 720 and height >= 560 , 1 image(this may be width < height)

second: width > height, 2 images.(left the first 1 image, do the rest 9 images ORDER BY RAND())

third: width >= 640, 2 images.(left above 3 images, do the rest 7 images ORDER BY RAND())

all the 5 images no repeat. My mind are confused now, need a help.

---

Well, if I understand your question correctly you want to do something like

(SELECT image FROM tab WHERE(first) ORDER BY RAND() LIMIT 1)
UNION
(SELECT image FROM tab WHERE(second) ORDER BY RAND() LIMIT 2)
UNION
(SELECT image FROM tab WHERE(third) ORDER BY RAND() LIMIT 2)

In the each WHERE clause replace the first, second and third with correct constraints...

---

SELECT id
FROM
  ( ( SELECT MIN(q) AS q,id
      FROM
        ( (SELECT 1 AS q, id FROM image WHERE(first) ORDER BY RAND() LIMIT 1)
        UNION ALL
          (SELECT 2,id FROM image WHERE(second) ORDER BY RAND() LIMIT 3)
        ) AS tmp2
      GROUP BY id
      ORDER BY MIN(q)
      LIMIT 3
    )
  UNION ALL
    (SELECT 3, id FROM image WHERE(third) ORDER BY RAND() LIMIT 5)
  ) AS tmp3
GROUP BY id
ORDER BY MIN(q)
LIMIT 5

---

You can order by multiple different values.

You can even order by the result of a boolean expression where true is treated as a higher value than false (so DESC gives you true first.)

You just put a comma between each ORDER BY clause.

Here is your example:

select * 
from foo 
ORDER BY
( width >= 729 AND height > 560 ) DESC,
( width > height ) DESC,
( WIDTH >= 640 ) DESC,
RAND()

Good luck.