Selecting a random row - 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

General SQL Server Forums

New to SQL Server Programming

Selecting a random row

Author

Topic

m4tt
Starting Member

13 Posts

Posted - 2008-03-04 : 08:41:06

Im trying to write a statement that only selects one of the possible rows that could be given.

For example:

Car ID: Colours Available:
1 Blue
1 Green
1 Black

I want the statement to return:

Car ID: Colours Available:
1 Blue(Random Selection)

Is this possible?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-04 : 08:42:52

SELECT ID,MAX(Colours Available)
FROM Car
GROUP BY ID

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-04 : 08:42:58

[code]Select top 1 * from table
order by newid()[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

m4tt
Starting Member

13 Posts

Posted - 2008-03-04 : 09:05:08

harsh, you idea seems to work well for me, but how can i do it if i want results for more cars, eg;

Car ID: Colours Available:
1 Blue
1 Green
1 Black
2 Blue
2 Red

Result:
1 Black
2 Blue

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-04 : 09:10:39

[code]SELECT t.ID ,t1.Color
FROM
(SELECT DISTINCT ID FROM Car) t
CROSS APPLY (SELECT TOP 1 Color
FROM Car
WHERE ID=t.ID
ORDER BY NEWID())t1 [/code]

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-03-04 : 09:41:18

In SQLSVR 2005:

select	CarID,
	Color
from	--Subquery
	(select	ROW_NUMBER() OVER (PARTITION BY CarID ORDER BY NewID()) as ROWNUMBER,
		CarID,
		Color
	from	Table1) Subquery
where	Subquery.ROWNUMBER = 1

e4 d5 xd5 Nf6

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-03-04 : 09:51:36

...or even ~~more obfuscatory~~ better:

;with SubQuery as
(select	ROW_NUMBER() OVER (PARTITION BY CarID ORDER BY NewID()) as ROWNUMBER,
	CarID,
	Color
from	Table1)
select	CarID,
	Color
where	ROWNUMBER = 1

e4 d5 xd5 Nf6

megbot
Starting Member

17 Posts

Posted - 2008-03-04 : 11:42:19

quote:
Originally posted by harsh_athalye

Select top 1 * from table
order by newid()

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

You can return however many you want. Select top 2 * from table, etc.
http://msdn2.microsoft.com/en-us/library/ms189463.aspx

:)
Meg

m4tt
Starting Member

13 Posts

Posted - 2008-03-04 : 12:03:32

Ive had a little bit of luck with getting newid() to work but its not giving me exatly what i want.

The actual system im trying to build is a computerised personal trainer (for working out at the gym)

Im trying to create somethine like this:

Day: ExerciseOrder: Muscle: Exercise:
1 1 Chest ChestEx1
1 2 Chest ChestEx2
1 3 Back BackEx4
1 4 Back Back Ex2
2 1 Legs LegsEx1
2 2 Legs LegsEx6
2 3 Arms ArmsEx4
2 4 Arms ArmsEx2

My DB has:
a table with the days:

Day: ExerciseOrder: Muscle:
1 1 Chest
1 2 Chest
1 3 Back
1 4 Back
2 1 Legs
2 2 Legs
2 3 Arms
2 4 Arms

And i have a table of exercises:
Muscle: Exercise:
Chest ChestEx1
Chest ChestEx2
Back BackEx1
Back Back Ex2
Legs LegsEx1
Legs LegsEx2
Arms ArmsEx1
Arms ArmsEx2

So i need the view to get the information from the table with the day, then for each row i need to select a random exercise that matches with the muscle.

Any ideas? Many, Many, Many Thanks

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-03-04 : 12:15:33

If you are using 2005, my solutions should work for you.

e4 d5 xd5 Nf6

m4tt
Starting Member

13 Posts

Posted - 2008-03-04 : 12:42:28

I am using 2005, i only have a tiny amount of experience with databases so am struggling a bit. Could you show me the statement i would need for these tables:

Days:
Day_No
Ex_Order
Muscle_ID

Exercises:
Muscle_ID
Exercies_ID

thanks again!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-03-04 : 13:06:04

[code];with RandomExercises as
(select ROW_NUMBER() OVER (PARTITION BY Days.Day_No, Days.Ex_Order ORDER BY NewID()) as ROWNUMBER,
Days.Day_No,
Days.Ex_Order,
Days.Muscle_ID,
Exercises.Exercise_ID
from Days
inner join Exercises on Days.Muscle_ID = Exercises.Muscle_ID)
select Day_No,
Ex_Order,
Muscle_ID,
Exercise_ID
from RandomExercises
where ROWNUMBER = 1[/code]

e4 d5 xd5 Nf6

m4tt
Starting Member

13 Posts

Posted - 2008-03-04 : 14:01:57

Thanks for the reply

Im getting the error: The OVER SQL construct or statement is not supported.

The query still works, but i just get 1 line. I really need an exercise being randomly generated for each of the rows. Im not sure if the error is stopping that.

Heres how i have the statement typed:
WITH RandomExercises AS (SELECT ROW_NUMBER() OVER (PARTITION BY dbo.user_splits.Day_No, dbo.user_splits.Exercise_Order
ORDER BY NewID()) AS ROWNUMBER, dbo.user_splits.Day_No, dbo.user_splits.Exercise_Order,
dbo.user_splits.Muscle_Group_ID, dbo.users_exercises.R_Exercise_ID
FROM dbo.user_splits INNER JOIN
dbo.users_exercises ON dbo.user_splits.Muscle_Group_ID = dbo.users_exercises.Muscle_Group_ID)
SELECT Day_No, Exercise_Order, Muscle_Group_ID, R_Exercise_ID
FROM RandomExercises
WHERE ROWNUMBER = 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-04 : 14:14:08

If you really are using Microsoft SQL Server 2005, change your compatibility level to 90 for the current database.

E 12°55'05.25"
N 56°04'39.16"

m4tt
Starting Member

13 Posts

Posted - 2008-03-04 : 14:39:30

I am def using 2005 and it is sent on level 90.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-03-04 : 14:48:58

Post the DDL for your tables.

e4 d5 xd5 Nf6

m4tt
Starting Member

13 Posts

Posted - 2008-03-04 : 15:00:57

ok, how do i get the dll? this is all still very new to me.
thanks

m4tt
Starting Member

13 Posts

Posted - 2008-03-05 : 05:03:00

I am now trying this statement but still only getting one line as a result that randomly generates the exercise.
What can u put in to give all (dbo.user_splits.Exercise_Order, dbo.user_splits.User_ID, dbo.user_splits.Split_ID, dbo.user_splits.Day_No) and get last column randomly generating for all of them

Thanks for your help guys.

Statement:

SELECT dbo.user_splits.Exercise_Order, dbo.user_splits.User_ID, dbo.user_splits.Split_ID, dbo.user_splits.Day_No,
(SELECT TOP (1) R_Exercise_ID
FROM dbo.users_exercises
ORDER BY NEWID()) AS Exercise, dbo.user_splits.Muscle_Group_ID
FROM dbo.user_splits INNER JOIN
dbo.users_exercises AS users_exercises_1 ON dbo.user_splits.Muscle_Group_ID = users_exercises_1.Muscle_Group_ID

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-05 : 05:11:44

how are you running the query? is it in a query window or with the query designer?

I only ask coz a quick google of your error suggests that it may be a problem if you're trying to use the 'query designer'

Em

m4tt
Starting Member

13 Posts

Posted - 2008-03-05 : 05:21:24

im just using the view builder tool.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-05 : 05:25:01

then perhaps that is the reason blindman's code didn't work for you. try it again in a blank query window

Em

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources