Random value for column - 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 2005 Forums

Transact-SQL (2005)

Random value for column

Author

Topic

coldfusioner
Starting Member

2 Posts

Posted - 2007-06-02 : 16:45:56

Give this table of data:

ID1 | ID2 | Type
-------------------------
3 | 5 | A
3 | 5 | B
4 | 7 | A
4 | 7 | B

I want to select distinct ID1 and ID2 pairs, and when there are dups, grab the value for Type randomly.

So one select could result in:
3 - 5 - A
4 - 7 - B

and another select could result in:
3 - 5 - B
4 - 7 - B

If I had a random aggregate it would be easy:

select ID1, ID2, random(Type) as Type
from orders
group by ID1, ID2

but we don't have one, so there must be something I can do instead

Thanks!
Daniel

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-03 : 01:29:48

how about this ?


declare @table table
(
	ID1	int, 
	ID2	int,
	Type	char(1)
)

insert into @table
select	3, 5, 'A'	union all
select	3, 5, 'B'	union all
select	4, 7, 'A'	union all
select	4, 7, 'B'	union all
select	5, 7, 'C'

select	ID1, ID2,
	Type	= case 	when count(*) > 1 
			then (select top 1 Type from @table x where x.ID1 = t.ID1 and x.ID2 = t.ID2 order by newid())
			else max(Type) 
			end
from	@table t
group by ID1, ID2

coldfusioner
Starting Member

2 Posts

Posted - 2007-06-03 : 01:49:56

Brilliant!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-09 : 07:35:32

Isnt this enough?


select	ID1, ID2,
	Type	= (select top 1 Type from @table x where x.ID1 = t.ID1 and x.ID2 = t.ID2 order by newid())
from	@table t

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources