Skip to content
Advertisement

SQL Select: Evenly Distribute Results Set

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

https://rextester.com/FIJDL68429

3 People found this is helpful
Advertisement