SQL repeat result set.

Danny Eeraerts

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?

11 Dec 2020

João Marques

MVP

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

11 Dec 2020

Danny Eeraerts

Thanks Joao.

11 Dec 2020

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.