patternsqlMinor
Splitting a deck of cards, and returning the results as if they were shuffled once
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:
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. :)
select top 26 * from DeckOfCards order by CardNumber desc
union all
select top 26 * from DeckOfCards order by CardNumber ascIt 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
SQLFiddle
Output:
Execution plans:
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.