Using UNION ALL with ORDER BY NEWID()

Question

Using UNION ALL with ORDER BY NEWID()

J.O.

Right there with Babe

Points: 756
More actions
August 30, 2008 at 8:18 pm

#197097

I have this stored procedure:
SELECT Name,NEWID()
FROM Persons
WHERE ADUserName = @ADUserName
UNION ALL
SELECT TOP 5 Name,NEWID()
FROM Persons
ORDER BY NEWID()
This works to an extent. However the 2nd query always returns the same 5 random records. If I run the 2nd query by itself, it always returns 5 different random records. How can I get this randomization to work for the 2nd query within the union, or is there another way to approach this? Thanks in advance.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

The Order BY you have affects the WHOLE query, UNION and all... you need to trick the 2nd query into a standalone query even in the presence of the UNION ALL... Like this...

SELECT Name, NEWID() AS ANewID

FROM Persons

WHERE ADUserName = @ADUserName

UNION ALL ----------------------------

SELECT *

FROM (SELECT TOP 5 Name,NEWID() AS ANewID

FROM Persons

ORDER BY NEWID())d

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

J.O. Right there with Babe Points: 756 More actions · Answer 2

J.O.

Right there with Babe

Points: 756

August 30, 2008 at 9:25 pm

#865447

AWESOME!! Thanks jeff

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Thanks for the feedback, Juan. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jazmin.hdz.hdz SSC Rookie Points: 43 More actions · Answer 4

jazmin.hdz.hdz

SSC Rookie

Points: 43

November 16, 2008 at 5:19 pm

#899118

Obrigada! 🙂

Thanks

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

De nada!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cassano7 Valued Member Points: 51 More actions · Answer 6

cassano7

Valued Member

Points: 51

May 3, 2011 at 9:15 am

#1319158

you're awesome jeff thanks a lot 😀

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

@cassano7

Thank you for the feedback :blush:... especially on such an old post.

Out of interest, what did you use this trick for?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Jeff Moden (5/17/2011)
@cassano7
Thank you for the feedback :blush:... especially on such an old post.
Out of interest, what did you use this trick for?

My guess is on randomizing data. Let's see what he has to say :-P.