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.

+3


source to share


2 answers


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.

+5


source


Assuming you have a relatively small number of ranks, this will work:



Order by
  case when rank <= n then 10
    when rank <= 2*n then 20
    when rank <= 3*n then 30
    when rank <= 4*n then 40
    when rank <= 5*n then 50 --more cases here if needed
    else 100 
  end
, group
, rank

      

+1


source







All Articles