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
x
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