SQL repeat result set.

Rank: #697

Solved

Question

I wrote this sql-statement to get a shuffle list of all cards from the database.

SELECT {Card}.[Shortname], {Card}.[Value] FROM {Card} ORDER BY NEWID(). This works, but I want a shuffled list pof all cards two times.

I expected that this was the solution:

SELECT {Card}.[Shortname], {Card}.[Value] FROM

(SELECT {Card}.[Shortname], {Card}.[Value] FROM {Card}

UNION ALL

SELECT {Card}.[Shortname], {Card}.[Value] FROM {Card})

ORDER BY NEWID()

but this doesn't work. Does anyone have any idea how to solve this problem?

on 2020-12-11

Rank: #70

Solution

Hi,

The only reason it doesn't work is because in a subquery you need to give it an alias:

SELECT cards.[Shortname], cards.[Value] FROM

(SELECT {Card}.[Shortname], {Card}.[Value] FROM {Card}

UNION ALL

SELECT {Card}.[Shortname], {Card}.[Value] FROM {Card}) cards

ORDER BY NEWID()

You can rename cards to a name of your liking.

Hope it helps.

Kind regards,
João

on 2020-12-11

Rank: #697

Thanks Joao.

on 2020-12-11