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