Need group by, but random - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need group by, but random

Author

Topic

ultradiv
Starting Member

41 Posts

Posted - 2006-03-02 : 06:31:10

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!!!)

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-02 : 07:09:26

Use

your_Query
Order by newid()

Madhivanan

Failing to plan is Planning to fail

ultradiv
Starting Member

41 Posts

Posted - 2006-03-02 : 07:15:59

quote:
Originally posted by madhivanan

Use

your_Query
Order by newid()

Thanks for that madhivanan, that orders the results randomly but I need the 'groups' ordered randomly not the whole results table
Cheers
Andy

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-02 : 07:58:25

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

ultradiv
Starting Member

41 Posts

Posted - 2006-03-02 : 08:17:33

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

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

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

I hope this is clear
Thanks
Andy

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-02 : 08:33:06

Try this

Select * from
(
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
) T
order by newid()

Madhivanan

Failing to plan is Planning to fail

ultradiv
Starting Member

41 Posts

Posted - 2006-03-02 : 08:47:51

this gave me:

DSAF 94 MR5I A 198 Technibel Gainable Multi Split 0 2800.0 850.0000 1
MMK-AP0092H A 272 Toshiba Murale DRV 0 2800.0 NULL 1
MUZ-GA25VAH A 51 Mitsubishi NULL Mono Split 1 2500.0 880.0000 1
SUZ-KA25VA A 64 Mitsubishi NULL Mono Split 1 2500.0 1000.0000 1
ORP999-XXP A 123 Demo Console Mono Split 0 3356.0 2356.0000 0
RAS-M10YDV-E A 211 Toshiba Gainable Multi Split 0 2500.0 760.0000 1
RAS-10UA-ES4 A 110 Toshiba NULL Mono Split 1 2730.0 540.0000 0

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-02 : 08:51:04

And that is wrong because .... ?

He just took your existing query, wrapped up it in a derived table, and then ordered it randomly. If your initial query isn't returning the results you need, that needs to be addressed before you can worry about how it should be sorted.

ultradiv
Starting Member

41 Posts

Posted - 2006-03-02 : 09:01:32

yeah I understand that, 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.

Andy

ultradiv
Starting Member

41 Posts

Posted - 2006-03-02 : 09:14:42

quote:
Originally posted by jsmith8858

If your initial query isn't returning the results you need, that needs to be addressed before you can worry about how it should be sorted.

The query is returing the results I need - in fact its in operation now, but I need to group the manufaturers together and not always have the group of mitsubishi's at the top

Thanks
Andy

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-03-02 : 09:40:01

Execute your query as is (including GROUP BY) with the addition of the ORDER BY NewID() at the end.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-02 : 09:42:37

quote:
Originally posted by ultradiv

this gave me:

DSAF 94 MR5I A 198 Technibel Gainable Multi Split 0 2800.0 850.0000 1
MMK-AP0092H A 272 Toshiba Murale DRV 0 2800.0 NULL 1
MUZ-GA25VAH A 51 Mitsubishi NULL Mono Split 1 2500.0 880.0000 1
SUZ-KA25VA A 64 Mitsubishi NULL Mono Split 1 2500.0 1000.0000 1
ORP999-XXP A 123 Demo Console Mono Split 0 3356.0 2356.0000 0
RAS-M10YDV-E A 211 Toshiba Gainable Multi Split 0 2500.0 760.0000 1
RAS-10UA-ES4 A 110 Toshiba NULL Mono Split 1 2730.0 540.0000 0

Does your query without order by return the same set of rows?
Do you think order by newid() returns additional data that are not returned in your query?

Refer this as an example



declare @t table(i int, data varchar(10))
insert into @t
	select 1, 'Mitsubishi' union all
	select 2, 'others' union all
	select 3, 'Toshiba' union all
	select 4, 'Mitsubishi' union all
	select 5, 'others' union all
	select 6, 'Toshiba' 
select * from @t where data='Mitsubishi' or data ='Toshiba' order by newid()

Run that for 5 or more times

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-02 : 09:43:53

quote:
Originally posted by ultradiv

yeah I understand that, 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.

Andy

YOu said it is returning the results you need, but then you say that it needs to be grouped differently ..... so, it is NOT returning the results you need.

Focus on your grouping and getting the basic results the way you want, and THEN worry about things like sorting once that is done. Don't try to fix two things at once -- one step at a time.

ultradiv
Starting Member

41 Posts

Posted - 2006-03-02 : 10:10:30

I'm sorry guys, I probably confused you with the results containing more than Mitsubishi and Toshiba - actually there are loads of Manufacturers and I just skimmed off the first few rows to show that the grouping was not correct

As to the code

quote:
declare @t table(i int, data varchar(10))
insert into @t
select 1, 'Mitsubishi' union all
select 2, 'others' union all
select 3, 'Toshiba' union all
select 4, 'Mitsubishi' union all
select 5, 'others' union all
select 6, 'Toshiba'
select * from @t where data='Mitsubishi' or data ='Toshiba' order by newid()

It dosent work properly because it will return ungrouped results e.g.
6 Toshiba
4 Mitsubishi
1 Mitsubishi
3 Toshiba

every Toshiba needs to be in the group of Toshibas

I agree that the results of the as is query do not return the results in the correct order - because they are not yet grouped.

Cheers
Andy

ultradiv
Starting Member

41 Posts

Posted - 2006-03-02 : 10:22:36

I just got this:
--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

ultradiv
Starting Member

41 Posts

Posted - 2006-03-02 : 10:49:28

or how about:
declare @R float
set @R = RAND()

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-02 : 11:23:59

So, are you all set?

ultradiv
Starting Member

41 Posts

Posted - 2006-03-03 : 05:45:37

yes thanks
I manged to get it working just fine - thanks for all your input, its much appreciated