5

Lets say I have a table with a deck of cards, numbered 01-52. I could return the top and bottom cards, as if I held each side of the union's select query in my left and right hands by doing a:

select top 26 * from DeckOfCards order by CardNumber desc
union all
select top 26 * from DeckOfCards order by CardNumber asc

It would be an even split.

But how could I have SQL Server intertwine the results returned, as if I had taken both portions of that union, one half in my left hand, and the other in my right, and shuffled them once like a deck of cards?

IE: CardNumber 52, followed by 1, in the following sequence: 52, 1, 51, 2, 50, 3, 49, 4, etc...

This is not a homework question, just one of those things that passes through my mind when trying to get some shut eye. :)

5

A little math can help achieving this

SELECT CardNumber
FROM   DeckOfCards 
ORDER BY (1 - CAST(CardNumber / 27 as bit)) * (CardNumber* 2)
       + (CAST(CardNumber/ 27 as bit)) * (1 + (52 - CardNumber) * 2)

CAST(CardNumber / 27 as bit) returns 0 for the card number below 27 and 1 for the number above 26, using this it's possible to create a different order for the two different block:

  • (1 - CAST(CardNumber / 27 as bit)) * (CardNumber* 2) put the first 26 card in the even position, as the first member will be 1 for those card and 0 for the other
  • (CAST(CardNumber/ 27 as bit)) * (1 + (52 - CardNumber) * 2) will put the second 26 card in the odd position, e.g. (1 + (52 - CardNumber) * 2) will return odd values in descending order

SQLFiddle example with the order formula as a second column to see how it works


It's possible to trade math with verbosity and do the manual steps:

  • Take the first half, and provide an added index
  • Take the second half, and provide an added index
  • Shuffle

But IMO it loses the magic attribute that goes well with card trick.
The CardNumber in the ShuffleID can be changed to Row_Number() if the former is not sequential, this second solution is functionally identical if not for one less JOIN to Paul White's one:

With _1H AS (
  Select Top 26
         CardNumber
       , Card
       , ShuffleID = CardNumber
  From   deck
  Order By CardNumber
), _2H AS (
  Select Top 26
         CardNumber
       , Card
       , ShuffleID = 53 - CardNumber
  From   deck
  Order By CardNumber Desc
), Shuffle As (
  SELECT CardNumber, Card, ShuffleID
  FROM   _1H
  UNION ALL
  SELECT CardNumber, Card, ShuffleID
  FROM   _2H
)
SELECT CardNumber, Card
FROM   Shuffle
ORDER BY ShuffleID, CardNumber Desc

SQLFiddle demo

  • Excellent answer, and nice trick with the order by. – beeks May 29 '14 at 17:08
  • 2
    FYI the extra join is intentional, to shuffle only ids (not any additional attributes). The second solution is indeed semantically similar, though it does not avoid a size-of-data sort, which was my stated aim. There are all sorts (ha!) of little issues like 53 - CardNumber not being an order-preserving function, but I don't suppose anyone except me will care about that. With an input of 52 rows, you're probably right not to care; however, on larger tasks, avoiding sorts by properly considering order preservation can be important. – Paul White 9 May 29 '14 at 19:19
  • @PaulWhite I never said that it was better or that the JOIN was wrong, I only pointed out the difference between the two, also thanks for the edit :) – Serpiton May 29 '14 at 19:30
6

I wanted to provide an alternative solution (using the sample data in Serpiton's answer - thanks!) that avoids sorts. This ought to be possible using ROW_NUMBER, but the query optimizer doesn't currently recognize its projection as unique. So:

DECLARE @TopHalf AS TABLE 
(
    id integer IDENTITY (1, 1) PRIMARY KEY, 
    CardID integer NOT NULL
);

DECLARE @BottomHalf AS TABLE 
(
    id integer IDENTITY (0,1) PRIMARY KEY, 
    CardID integer NOT NULL
);

INSERT TOP (26) @TopHalf (CardID)
SELECT D.id
FROM dbo.deck AS D
ORDER BY D.id ASC;

INSERT TOP (26) @BottomHalf (CardID)
SELECT D.id
FROM dbo.deck AS D
ORDER BY D.id DESC;

SELECT 
    D.id,
    D.[card]
FROM
(
    SELECT id, CardID FROM @TopHalf AS TH
    UNION
    SELECT id, CardID FROM @BottomHalf AS BH
) AS Shuffled
JOIN dbo.deck AS D
    ON D.id = Shuffled.CardID
ORDER BY 
    Shuffled.id, 
    Shuffled.CardID;

SQLFiddle

Output:

Output

Execution plans:

Execution plans

0

The key is to have an ORDER BY that can work with the data from both sets. I used ROW_NUMBER to get a bit more general solution, but it would be possible to avoid the sorts by doing direct math on the card numbers.

WITH DeckOfCards (CardNumber) AS (
    SELECT TOP 52
        number
    FROM master.dbo.spt_values
    WHERE type = 'P' AND number > 0
),
Ranks AS (
    SELECT *
    ,ROW_NUMBER() OVER (ORDER BY CardNumber ASC) AscOrder
    ,ROW_NUMBER() OVER (ORDER BY CardNumber DESC) DescOrder
    FROM
        DeckOfCards
)
SELECT
    CardNumber
FROM
    Ranks
    CROSS APPLY (
        SELECT
            MAX(n) AS Ranking
            , CASE WHEN DescOrder > AscOrder THEN 1 ELSE 0 END AS DeckHalf
        FROM (VALUES (AscOrder), (DescOrder)) AS T(n)
    ) A
ORDER BY
A.Ranking DESC, A.DeckHalf

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.