Returning Grouped Random Results

Let's say you have data and you want to return this random but grouped, what do I mean by that? For example you have the following data
sony 4
sony 6
toshiba 1
toshiba 3
mitsubishi 2
mitsubishi 5

When you rerun the query you want the order to be different but the companies have to be grouped together. So the next result could be something like this

toshiba 1
toshiba 3
sony 4
sony 6
mitsubishi 2
mitsubishi 5


To order a result set randomly every single time is easy you just have to add ORDER BY NEWID() and it will be different every single time

To order randomly and keeping it 'ordered/grouped' by company is a little bit trickier
Below are 2 ways to accomplish that. The first way is with a sub query the second is by using RAND and CHECKSUM
If you run the two queries at the same time and look at the execution plan you will notice that the query cost for the query with the sub query is 66.75% (relative to the batch) and for the query with RAND and CHECKSUM it's only 33.25%


For those of you who don't know how to see the execution plan, this is how you do that
CTRL + K (you can also select Query-->Show Execution Plan from the menu bar) then highlight both queries and press F5. Between Grids and Messages you will see a new tab named Execution Plan, click on that tab and you will see a graphical representation of the execution plan


CREATE TABLE #Testcompanies (
Name VARCHAR(50),
ID INT)


INSERT INTO #Testcompanies
SELECT 'toshiba' ,1
UNION ALL
SELECT 'mitsubishi', 2
UNION ALL
SELECT 'toshiba', 3
UNION ALL
SELECT 'sony', 4
UNION ALL
SELECT 'mitsubishi', 5
UNION ALL
SELECT 'sony', 6



--Query using a sub query and NEWID()
SELECT T.*
FROM #Testcompanies T
JOIN (SELECT DISTINCT TOP 100 PERCENT Name,
NEWID() AS GroupedOrder
FROM #Testcompanies
GROUP BY Name
ORDER BY NEWID()) Z
ON T.Name = Z.Name
ORDER BY Z.GroupedOrder


--Query using RAND()
DECLARE @R FLOAT
SET @R = RAND()

SELECT TOP 100 PERCENT *
FROM #TESTCOMPANIES
ORDER BY RAND(@R * CHECKSUM(NAME))