Given this exemplary relation and data
|_id_____|_type____|_name________|_date_______________|
| 1 | 1 | ajshdf | 2012-02-18 12:35:00
| 2 | 1 | jkhae | 2012-02-18 12:35:00
| 3 | 1 | dsfad | 2012-02-18 12:35:00
| 4 | 2 | 3f3ad | 2012-02-18 12:35:00
| 5 | 2 | gad64 | 2012-02-18 12:35:00
| 6 | 2 | as3da | 2012-02-18 12:35:00
| 7 | 3 | faf3a | 2012-02-18 12:35:00
| 8 | 3 | ga3d3 | 2012-02-18 12:35:00
| 9 | 4 | jzd64 | 2012-02-18 12:35:00
What is the best way to achieve a resulset that will distribute the records as evenly as possible, ordered by the types?
What I want is that the result records are ordered in a manner that the newest entries are always on top, but that the types are altered around, so that the result looks something like this:
|_id_____|_type____|_name________|_date_______________|
| 1 | 1 | ajshdf | 2012-02-18 12:35:00
| 4 | 2 | 3f3ad | 2012-02-18 12:35:00
| 7 | 3 | faf3a | 2012-02-18 12:35:00
| 9 | 4 | jzd64 | 2012-02-18 12:35:00
| 2 | 1 | jkhae | 2012-02-18 12:35:00
| 5 | 2 | gad64 | 2012-02-18 12:35:00
| 8 | 3 | ga3d3 | 2012-02-18 12:35:00
| 3 | 1 | dsfad | 2012-02-18 12:35:00
| 6 | 2 | as3da | 2012-02-18 12:35:00
| 7 | 3 | faf3a | 2012-02-18 12:35:00
As you see, the type is altered and cycled in the result - 1, 2, 3, 4, 1, 2, 3, 1, 2, 3
Pretty inefficient but I think it will work:
SELECT a.*
FROM
TableX AS a
JOIN
TableX AS b
ON b.type = a.type
AND b.id <= a.id
GROUP BY
a.id
ORDER BY
COUNT(*)
, a.id