Page 1 of 1

How, Order by rand and NAME - LIMIT

Posted: Thu Sep 30, 2010 8:31 pm
by alvaro
hi guys i have a table categorys (there is about 100 registers)

i need to make a select

like

Code: Select all

 Select * from categorys where status=1 ORDER BY RAND(), NAME ASC LIMIT 30 
this was my logic for make this but not working

so i need to get 30 register random, then the 30 register order by NAME ASC

can anybody help me how i can do this?

also i tried

Code: Select all

 select * from categorys where status=1 ORDER BY RAND() AND NAME ASC LIMIT 30
on wall situations i get 30 register random but not ordenad by NAME ASC

Re: How, Order by rand and NAME - LIMIT

Posted: Thu Sep 30, 2010 8:53 pm
by requinix
The first item in an ORDER BY will sort the entire table. Further items only come into play when the sorting is stuck between multiple identical values. RAND(), NAME will randomize the table, and only if there are two identical RAND() values will it then try to sort those rows by the name.

Code: Select all

SELECT * FROM (
    SELECT * FROM `categorys` WHERE `status` = 1 ORDER BY RAND() LIMIT 30
) t
ORDER BY `NAME` ASC

Re: How, Order by rand and NAME - LIMIT

Posted: Sat Oct 02, 2010 5:02 pm
by alvaro
i did like this:

Code: Select all

 "SELECT * FROM ( SELECT * FROM categorys ORDER BY rand() LIMIT 30 ) categorys WHERE status=1 ORDER BY NAME ASC LIMIT 30"
works perfect...

Re: How, Order by rand and NAME - LIMIT

Posted: Sat Oct 02, 2010 5:17 pm
by DigitalMind
You could say "thanks" to tasairis for the solution.
More than that, your solution is wrong!

Re: How, Order by rand and NAME - LIMIT

Posted: Sat Oct 02, 2010 5:54 pm
by alvaro
DigitalMind wrote:You could say "thanks" to tasairis for the solution.
More than that, your solution is wrong!
My solution works, if is wrong or not i dont know but works, the another solution by tasairis i dont tried...

Re: How, Order by rand and NAME - LIMIT

Posted: Sat Oct 02, 2010 7:54 pm
by requinix
You're going to discover that sometimes you get less than 30 results.