Generate a Random Value from a Table

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

General SQL Server Forums

New to SQL Server Programming

Generate a Random Value from a Table

Author

Topic

adya
Starting Member

31 Posts

Posted - 2008-12-08 : 14:39:37

Hi

I have 2 tables, Color and ColorInUse
Color: ColorId, ColorValue
ColorInUse: ColorNo, InUse, Branch

I have to get a single random ColorValue from Color where ColorInUse.InUse = false and Branch = int

How do i write the SQL Select statement for that?

Thanx in advance!

Adya

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-08 : 14:42:16

[code]select top 1 c.colorvalue
from color c inner join colorinuse i
on c.colorid = colorno
where i.inuse = 0 and i.branch = 'int'
order by newid() asc
[/code]

adya
Starting Member

31 Posts

Posted - 2008-12-08 : 14:52:29

In this case,
* Do i need to supply the value for the colorno in the on clause?

For my usage, I have to retrieve values from Color and then insert the values in the ColorInUse for InUse and branch. I need values from Color table which have no entry for a particular branch in ColorInUse table.

Thanx in advance!

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-08 : 15:12:41

Are you saying you want to insert a new record to COLORINUSE table?? The way you described is a little confusing, please illustrate.

adya
Starting Member

31 Posts

Posted - 2008-12-08 : 15:17:40

Sorry for the description, it did sound a lil confusing.

I am saying that I have a table Color: which stores ColorId and a ColorValue (Hex) of a Color. I need to pick out the random color from Color table, which might have a entry in the table ColorInUse, which I want to filter out on the basis of InUse and BranchId. A same branch can't use the same color twice.

Hope this is clear for helping me.

Thanx!

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-08 : 15:30:44

So you want to pick a random color if color is NOT in use or does not exists in the branch.


Select top 1 colorvalue
from color c
where not exists (select 1 from colorinuse where colorno = c.colorid
and inuse = 1 and branch = 'int' )
order by newid() asc

adya
Starting Member

31 Posts

Posted - 2008-12-08 : 15:39:01

I want a random color if color does not exists in the branch.

I tried running the query, it runs, but it produces no output.
My tables have the following data

Color:
1 #FF0000
2 #0000FF
3 #0000CC
4 #0033FF
where order is ColorId, ColorValue

ColorInUse
1 1 1101
1 1 1102
4 1 1107
3 1 1102
where, Order is ColorId, InUse, BranchId

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-08 : 15:43:17

See http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.63"
N 56°04'39.26"

adya
Starting Member

31 Posts

Posted - 2008-12-08 : 16:02:48

Sorry Peso

I am re-stating my questions, I was in hurry, thus couldn't clarify myself.

Ques: I need to get a random color value from table: Colors

Table Structure:
Colors(ColorNo int, ColorValue varchar(10))
ColorInUse(ColorId int, InUse bit, BranchId int)

Relationship ColorId is a foriegn key, and has the values as ColorNo(Colors Table)

Please help me with the query, where conditions are
1. Color can't be re-assigned to a same branch, while in use by the same branch
2. Color can't be re-assigned till InUse = 1

Any help would be appreciated!

Thanx!

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-08 : 16:13:23

[code]

declare @color table (colorid int, colorvalue varchar(6))
declare @colorinuse table (colorno int, inuse bit, branch INT)

insert into @color
select 1,'FF0000'
union all
select 2, '0000FF'
union all
select 3, '0000CC'
union all
select 4, '0033FF'

insert into @colorinuse
select 1, 1, 1101
union all
SELECT 1,1, 1102
union all
SELECT 4,1, 1107
union all
SELECT 3,1, 1102

Select top 1 *
from @color c
where not exists (select 1 from @colorinuse where colorno = c.colorid
and inuse = 1 and branch = 1107)
order by newid() asc
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 02:52:14

quote:
Originally posted by adya

Sorry Peso

I am re-stating my questions, I was in hurry, thus couldn't clarify myself.

Ques: I need to get a random color value from table: Colors

Table Structure:
Colors(ColorNo int, ColorValue varchar(10))
ColorInUse(ColorId int, InUse bit, BranchId int)

Relationship ColorId is a foriegn key, and has the values as ColorNo(Colors Table)

Please help me with the query, where conditions are
1. Color can't be re-assigned to a same branch, while in use by the same branch
2. Color can't be re-assigned till InUse = 1

Any help would be appreciated!

Thanx!

would have been much better if you post question in format as in link with some sample data.

adya
Starting Member

31 Posts

Posted - 2008-12-09 : 04:40:27

Thanx hanbingl!
Got it!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources