I have a table called TableA, with a Color column.
Nine rows total, if I select * from TableA order by Color, I get results set
blue blue blue green green green red red red
how do I distribute the colors in a select statement, such that, the results set is
blue green red blue green red blue green red
Note: This sample shows an equal number of each color. I’m hoping for a solution that could handle a non-equal number of each color.
Here’s another example:
NAME, eye color John, blue Joe, blue Jim, blue Jerry, blue Jack, blue Jamie, blue Steve, green Sam, green Sally, green Sara, green Mark, brown Max, brown Mary, brown Nathan, red Nick, red
possible results:
John, blue Steve, green Mark, brown Nathan, red Joe, blue Sam, green Max, brown Nick, red Jim, blue Sally, green Mary, brown Jerry, blue Sara, green Jack, blue Jamie, blue
Advertisement
Answer
You can make use of a window function/subquery:
SELECT COLOR FROM( SELECT COLOR, ROW_NUMBER() OVER(PARTITION BY COLOR ORDER BY COLOR) AS RN FROM #TEMP_A ) AS X ORDER BY RN,COLOR