randomise SQL results

Question

On my webpage i grab my data of a datebase and show this on a result page. I'd like to sort the results and in order by (thats the translation of google translate). What i mean is: i've got a field called "type" and it has the value a, b or c. When i select the data it returns the results as

a a a a b b b c c c

but i like to see it as

a b c a b c a b c a

My question, what is the best solution for this (is it possible with a SQL query?)

Thanks for your help!

It would help if you provided the structure of the table you are referring to.. if you have a primary key, and are able to add the rows in the order abcabcabc, then you can simply sort by the primary key. — Steve, Mar 11 '12 at 19:48
The table has a primary key but it is not easy/possible to insert them in the correct order — Jab, Mar 11 '12 at 19:56
So you don't want "random" ordering, you want to avoid two identical values in a row? What if the data is `a a a a a b c` for example? Maybe I misinterpreted the question. Either way: needs clarification and a clear spec. — Wesley Murch, Mar 11 '12 at 20:26
On my website im comparing multiple providers in one overview.I want each result to be from a different provider and when there are no unique providers left i want to start over beginning with the first. I want to repeat this until i run out of results. — Jab, Mar 11 '12 at 21:12
are the values in the "type" field predefined ? or is it that new types be added dynamically by the users ? — , Mar 11 '12 at 22:22
I add the types myself in the cms and they are stored in an other table called providers. — Jab, Mar 11 '12 at 22:42
great. so you can change the query when you add new types. i will post an answer soon. — , Mar 11 '12 at 22:43

score 0 · Accepted Answer · edited May 23 '17 at 10:34

The idea behind the solution is to make the rows be numbered from 1 and up based on the provider. if the rows with type 'a' where numbered from 1..4 and rows with type 'b' where numbered from 1 ..3 and rows with type 'c' where numbered from 1..3, then you can easily get what you wanted with using "order by" on these numbers.

for the example, i will suppose that your table name is "my_table" and it has two fields, type and data.

in order to make rownum in mysql, i will use what was described here: rownum in mysql

now lets suppose we want to select all rows with type 'a' and give them ascending row number. we can do it like the following:

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

we can do the same for the other types, then union all the results, and then wrap them in an outer select that does the ordering. the answer to your question would be the following query:

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

note that for each type we define a different variable to do the counter.

as a final note, you could do it with defining all counters in the same join and then you don't use the union all, but instead in the select you can use the correct variable based on the type. the following is an equal query

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

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

That does perfectly the job! thank you very much! Although I don't understand the the working of the script completly. Am i right with the following: The first select selects all the data, the second select runs trough all the selected data with a statement similar to the switch statement. If the type is the same it gives a number to it, temporary saved in order_num. the part after the second from is the part i don't understand. If you'd like, would you explain this so i get to understand it completly? — Jab, Mar 12 '12 at 15:26
what you refer to as "second select" is the sub query. it runs first and gathers all the data and assigns the order_num. then, the hihger select runs and it can use "order by" on the fields order_num and type to sort the data. — , Mar 12 '12 at 15:40

randomise SQL results

1 Answers1