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

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:

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