CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-order-by
Jonathan Wilson
Jonathan Wilson

Reputation: 4305

Order by pairs of values

I have a set of rankings, ordered by group and ranking:

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 interleave the 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 have achieved the desired result with loops and table variables (code pasted here because I got a non-descript syntax error in a SQL Fiddle):

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 with a set-based approach (no loops, no table variables)?

I'm using SQL Server Enterprise 2008 R2.

Edit: To clarify, I need no more than n rows per group to appear contiguously. The goal of this query is to yield an ordering, when read sequentially, offers an equal representation (n rows at a time) of each group, with respect to rank.

Upvotes: 3

Views: 353

Answers (2)

xQbert
xQbert

Reputation: 35333

Perhaps something like this...SQL FIDDLE

Order by
 Ceiling(rank*1.0/2), group, rank

Working fiddle above (column names changed slightly)

Updated: was burned by int math... . should work now. forcing int to decimal by multiplying by 1.0 so implicit casting doesn't drop the remainder I need for ceiling to round correctly.

Upvotes: 5

APH
APH

Reputation: 4154

Assuming you have a relatively low number of ranks, this would 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

Upvotes: 1

Related Questions