# Splitting a deck of cards, and returning the results as if they were shuffled once

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. :)

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

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:

Execution plans:

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