Order by pairs of values
I have a set of rankings ordered by group and rank:
Group | Rank ------------ A | 1 A | 2 A | 3 A | 4 A | 5 A | 6 B | 1 B | 2 B | 3 B | 4 C | 1 C | 2 C | 3 C | 4 C | 5 D | 1 D | 2 D | 3 D | 4
I want to alternate groups, ordered by group and rank, n rankings per group at a time (here n = 2):
Group | Rank ------------ A | 1 A | 2 B | 1 B | 2 C | 1 C | 2 D | 1 D | 2 A | 3 A | 4 B | 3 B | 4 C | 3 C | 4 D | 3 D | 4 A | 5 A | 6 C | 5
I achieved the desired result with loops and table variables (code pasted here because I got a syntax error with no description in the SQL script):
CREATE TABLE Rankings([Group] NCHAR(1), [Rank] INT) INSERT Rankings VALUES ('A',1), ('A',2), ('A',3), ('A',4), ('A',5), ('A',6), ('B',1), ('B',2), ('B',3), ('B',4), ('C',1), ('C',2), ('C',3), ('C',4), ('C',5), ('D',1), ('D',2), ('D',3), ('D',4) -- input DECLARE @n INT = 2 --number of group rankings per rotation -- output DECLARE @OrderedRankings TABLE([Group] NCHAR(1), Rank INT) -- -- in-memory rankings.. we will be deleting used rows DECLARE @RankingsTemp TABLE(GroupIndex INT, [Group] NCHAR(1), Rank INT) INSERT @RankingsTemp SELECT ROW_NUMBER() OVER (PARTITION BY Rank ORDER BY [Group]) - 1 AS GroupIndex, [Group], Rank FROM Rankings ORDER BY [Group], Rank -- loop variables DECLARE @MaxGroupIndex INT = (SELECT MAX(GroupIndex) FROM @RankingsTemp) DECLARE @RankingCount INT = (SELECT COUNT(*) FROM @RankingsTemp) DECLARE @i INT WHILE(@RankingCount > 0) BEGIN SET @i = 0; WHILE(@i <= @MaxGroupIndex) BEGIN INSERT INTO @OrderedRankings ([Group], Rank) SELECT TOP(@n) [Group], Rank FROM @RankingsTemp WHERE GroupIndex = @i; WITH T AS ( SELECT TOP(@n) * FROM @RankingsTemp WHERE GroupIndex = @i ); DELETE FROM T SET @i = @i + 1; END SET @RankingCount = (SELECT COUNT(*) FROM @RankingsTemp) END SELECT @RankingCount as RankingCount, @MaxGroupIndex as MaxGroupIndex -- view results SELECT * FROM @OrderedRankings
How can I achieve the desired ordering using a set based approach (no loops, no table variables)?
I am using SQL Server Enterprise 2008 R2.
Edit: To clarify, I need no more than
lines per group for them to appear contiguous. The purpose of this query is to give ordering, when read sequentially, gives an equal representation (n rows at a time) of each group with respect to rank.
source to share
Maybe something like this ... SQL FIDDLE
Order by Ceiling(rank*1.0/2), group, rank
Working fiddle above (column names changed slightly)
Updated: int math was burned .... should work now. forcing the int to do the decimal multiplication by 1.0, so the implicit casting does not discard the remainder needed to round off the ceiling correctly.
source to share