HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
oncetheshuffledcardsdecksplittingwerereturningandresults

Problem

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

Solution

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:

Code Snippets

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;

Context

StackExchange Database Administrators Q#66118, answer score: 6

Revisions (0)

No revisions yet.