php - randomise SQL results -

on webpage grab data of datebase , show on result page. i'd sort results , in order (thats translation of google translate). mean is: i've got field called "type" , has value a, b or c. when select data returns results

a a b b b c c c

but see

a b c b c b c

my question, best solution (is possible sql query?)

thanks help!

the idea behind solution make rows numbered 1 , based on provider. if rows type 'a' numbered 1..4 , rows type 'b' numbered 1 ..3 , rows type 'c' numbered 1..3, can wanted using "order by" on these numbers.

for example, suppose table name "my_table" , has 2 fields, type , data.

in order make rownum in mysql, use described here: rownum in mysql

now lets suppose want select rows type 'a' , give them ascending row number. can following:

select type,data,@rownuma := @rownuma+1 order_num my_table,(select @rownuma:=0) ra type='a' 

we can same other types, union results, , wrap them in outer select ordering. answer question following query:

select type,data ( select type,data,@rownuma := @rownuma+1 order_num my_table,(select @rownuma:=0) ra type='a' union select type,data,@rownumb := @rownumb+1 order_num my_table,(select @rownumb:=0) rb type='b' union select type,data,@rownumc := @rownumc+1 order_num my_table,(select @rownumc:=0) rc type='c' ) in_tab order order_num,type 

note each type define different variable counter.

as final note, defining counters in same join , don't use union all, instead in select can use correct variable based on type. following equal query

select type,data ( select type,data,        case when type='a' @rownuma := @rownuma+1              when type='b' @rownumb := @rownumb+1             when type='c' @rownumc := @rownumc+1        end order_num my_table, (select @rownuma:=0) ra, (select @rownumb:=0) rb, (select @rownumc:=0) rc  ) in_tab order order_num,type 

the second version better if have more conditions (where clause) selecting rows won't need repeat them in every sub query part of union in first version.