Selecting random rows

Question

Selecting random rows

m4tt

SSC Enthusiast

Points: 169
More actions
March 4, 2008 at 4:20 pm

#203824

The 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?
p.s. ive only been using 2005 for a few days

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 1

I sure do... read the URL in my signature. People will come screaming out of the woodwork to answer your question almost before you can ask it if you follow those simple guidlines...

In the meantime, all you need to do is use a join like you normally would... but you're going to do an ORDER BY NEWID() on the table you want to randomly select from and use a TOP 1 to narrow it down to a single row.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 2

What?

He asked for cars here...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98384

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

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 3

Based on that link, I'm thinking this is someone's homework... wait a minute... I've got a porkchop around here somewhere... maybe even a Yak Burger, huh, Peter?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

m4tt SSC Enthusiast Points: 169 More actions · Answer 4

This is for my finaly year project at university so i suppose you could say its homework. But being worth such a large amount of my degree is worth alot. This is why i am using more than one source to try and resolve my problem. Thanks for ur help

m4tt SSC Enthusiast Points: 169 More actions · Answer 5

I have tried this statement but its only replying 1 row. I need it to reply all rows from user_split table and randomly generate the last column. Any ideas?

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

thanks guys!

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 6

You have the correct idea... but since you didn't post anything about what the "user_splits" table looks like or what data it has in it, I have no idea what you're doing wrong.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)