SQL: Using SORT to group same values together while mixing different values randomly

Let’s say I have a table Songs(title,songwriter). I want to get a list of songs where songs of the same songwriter appear in one block. So i just sorted:

SELECT * FROM Songs ORDER BY songwriter ASC

But now it would be nice if different songwriters would not always appear in the same order. I want an output where the songwriters would not always be ordered like

a,a,a,b,c,c,d,e,e,e,e

but also like

b,c,c,a,a,a,e,e,e,d

for instance. I know that i could use

SELECT songwriter FROM Songs GROUP BY songwriter ORDER BY RAND()

and then just form a query for each songwriter in the return’s order but I’d really like just to use one query to have a single result-table.

The only idea I had so far was ordering by a hash-function which gets chosen at a random base each time the query is executed. This would let same songwriters appear after each other while mixing up different values. But it also seems to be too complicated and I do not know how to implement choosing the hash each time in a nice way.

Is there any clean way to get the task done with only one result-table? Is it maybe possible to GROUP and then split the groups again in one query?

(I am using MySQL)

Answer

One method is to use rand() but in a subquery in the FROM clause:

SELECT *
FROM Songs s JOIN
     (SELECT songwriter, rand() as rnd
      FROM (SELECT DISTINCT songwriter FROM s) s
     ) ss
     ON ss.songwriter = s.songwriter
ORDER BY ss.rnd, ss.songwriter ASC;

You can also hash the value, but I don’t think there is a simple hash function available out-of-the-box in MySQL.

Advertisement

Answer