SQL -> ordering a rand() result?

niekos

Oke guys, kinda got a question here.

I want to pick some random rows from my mysql db and ordering them on a field (domain). The problem is that when i do:

SELECT *
FROM mail
ORDER BY rand( ), domain ASC

then he doesn't order at all. Only randomize

and

SELECT *
FROM mail
ORDER BY domain ASC,rand( )

doesn't randomize. Only sort.

Well i want the randomized fields to be orderded.
I don't want to order it with php. (i like to get all mine data sorted and clean from the db). Please can anyone help me out? Well thanks for reading at least🙂

nemonoman

You need to better describe the results you want.

ORDER BY rand( ), domain ASC

Will order the domains randomly.

ORDER BY domain ASC,rand( )

will order the domains alphanumerically.

What are you trying to do?

Are you trying to get a SELECTION of random domains, and then order that SELECTION?

If so the RAND() needs to be in your SELECT, not in your ORDER BY clause.

niekos

That was exactly what i wanted to do. Ordering the rand() result. What you are saying seems very logical so i'm going to try it. Thanx 🙂

(sorry for the late reply but i only visit forums on my work 😛 )

niekos

oke so i tryed
SELECT rand(),* FROM mail ORDER BY domain ASC LIMIT 3

And (kinda what i expected) just a orderd result. The rand() gives me some random numbers. But he doesn't random pick rows.

But then again i got this new idea because of your post:

SELECT *,rand() as t1 FROM mail ORDER BY t1,domain ASC LIMIT 3

And i got what i want 🙂 woeiii

And what i really found odd is that
SELECT * FROM mail ORDER BY rand() LIMIT 3

Is just as fast... uhm slow (0.2 sec on a 16000 field db).

Oke so i got it. Now i can finnaly proceed with my email application 🆒