Need group by, but random

[Ultradiv]

Hi Helpfuls
I am having difficulty working out how to group the results of a select into groups but those groups need to be ordered randomly.
So this is my query without grouping,

SELECT dbo.Machines.MachName, dbo.Machines.class, dbo.Machines.MachineID,
dbo.Companies.Name, dbo.Implimentation.Location, dbo.Machine_categories.CatName,
dbo.Machines.Units, dbo.Machines.CoolingPower, dbo.Machines.CatPrice, dbo.Machines.Reversable
FROM dbo.Machines INNER JOIN dbo.Companies ON
dbo.Machines.ManID = dbo.Companies.ManID
LEFT OUTER JOIN dbo.Implimentation ON
dbo.Machines.LocationID = dbo.Implimentation.LocationID
INNER JOIN dbo.Machine_categories ON
dbo.Machines.CategoryID = dbo.Machine_categories.CatID
Where dbo.Machines.CoolingPower between 2500 and 3499

What needs to happen is the results need to be grouped by dbo.Companies.Name (or dbo.Companies.ManID [which is the PK of dbo.Companies]) but each time the query is fired I want the groups in a different order, so that the Companies that manufacture these machines are not always in the same position in the list (they get upset when their competitior is always first in the list!!!)

Result of query as is
MSZ-FA25VA(H) A 41 [red]Mitsubishi[/red] Murale Mono Split 0 2500.0 670.0000 1
RAS-10NKV-E A 42 [blue]Toshiba[/blue] Murale Mono Split 0 2500.0 535.0000 1
MUZ-FA25VA A 45 [red]Mitsubishi[/red] NULL Mono Split 1 2500.0 1020.0000 1
RAS-B10EKVP-E A 43 [blue]Toshiba[/blue] Murale Mono Split 0 2500.0 850.0000 1
MUZ-FA25VAH A 46 [red]Mitsubishi[/red] NULL Mono Split 1 2500.0 1200.0000 1

Required result first time (random)
MSZ-FA25VA(H) A 41 [red]Mitsubishi[/red] Murale Mono Split 0 2500.0 670.0000 1
MUZ-FA25VA A 45 [red]Mitsubishi[/red] NULL Mono Split 1 2500.0 1020.0000 1
MUZ-FA25VAH A 46 [red]Mitsubishi[/red] NULL Mono Split 1 2500.0 1200.0000 1
RAS-10NKV-E A 42 [blue]Toshiba[/blue] Murale Mono Split 0 2500.0 535.0000 1
RAS-B10EKVP-E A 43 [blue]Toshiba[/blue] Murale Mono Split 0 2500.0 850.0000 1

Required result second time (random)
RAS-10NKV-E A 42 [blue]Toshiba[/blue] Murale Mono Split 0 2500.0 535.0000 1
RAS-B10EKVP-E A 43 [blue]Toshiba[/blue] Murale Mono Split 0 2500.0 850.0000 1
MSZ-FA25VA(H) A 41 [red]Mitsubishi[/red] Murale Mono Split 0 2500.0 670.0000 1
MUZ-FA25VA A 45 [red]Mitsubishi[/red] NULL Mono Split 1 2500.0 1020.0000 1
MUZ-FA25VAH A 46 [red]Mitsubishi[/red] NULL Mono Split 1 2500.0 1200.0000 1
what the problem is is the lack of my ability to be able to group the results by Companies.name. Once all the companies are grouped into all Toshiba's together and all Mitsubishi's together then I need to randomly order those groups

I could have ordered the query as is with
order by Companies.name

But they will only be grouped and ordered alphanumerically and therefore the same result each firing of the query, and as I said I need a different order of those groups each firing.


I really hope you can help me out here,
Many thanks
Andy

 

[SQLDenis]

just add order by newid()

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

forget newid(), that that won't work in this case

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Ultradiv]

yeah its more complicated than that
Andy

 

[vongrunt]

> So this is my query without grouping

Post query with grouping. And some sample data if possible.

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[SQLDenis]

hehehe
here we go, tricky one, modify for your own use
run the code to understand how it works, keep hitting F5

--make up some data
create table #testcompanies (name varchar(50),id int)
insert into #testcompanies
select 'toshiba' as p,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

--this is what you would use hit F5 for this part only
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

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Ultradiv]

Cracking Toast Grommit... er Dennis!!

Thanks a stack, I know where to come in the future
Andy

 

[vongrunt]

How about simply

order by name, newid()

?

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[SQLDenis]

select * from #testcompanies
order by name,newid()


is always the same order

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Ultradiv]

NOt quite
within each group they are random

 

[vongrunt]

Ah... yes. Decaf crisis .

Maybe also this:

declare @R float
set @R = RAND()

select top 100 percent* from 
#testcompanies
order by rand(@R * checksum(name)) --, whatever

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[Ultradiv]

wow! thats a good one!
cheers
Andy