sql random help - 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)

sql random help

Author

Topic

android.sm
Starting Member

36 Posts

Posted - 2010-07-04 : 10:30:08

i have three tables (Category, PhilosophyAuthors, PhilosophyQuote). the idea is that my front end app will allow user to select a category from a list, based on this list select an author. the get quote button should then pick out a random quote from that author. my stored procedure needs to do this...i need help with get random quotes from the table. my SP looks like this:

ALTER PROCEDURE [dbo].[GetQuote] (@AuthorId int)
AS
SELECT DISTINCT PhilosophyQuote.Quote
FROM PhilosophyQuote
WHERE PhilosophyQuote.Id = @AuthorId

this simply gets me a quote based on author. i want to get random quote from the author - any suggestions?

thanks all

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-04 : 10:58:48

SELECT top 1 PhilosophyQuote.Quote
FROM PhilosophyQuote
WHERE PhilosophyQuote.Id = @AuthorId
order by newid()

No, you're never too old to Yak'n'Roll if you're too young to die.

android.sm
Starting Member

36 Posts

Posted - 2010-07-04 : 16:08:47

thanks for your code. however, when i execute the code it returns only the first row. this author has an author id = 1. and he has few quotes. therefore i was expecting it to randomly select one of his quotes. any way i can get his quotes randomly? thanks...

android.sm
Starting Member

36 Posts

Posted - 2010-07-04 : 16:36:42

ok i have this working now.

select top 1 Quote from PhilosophyAuthors pa, PhilosophyQuote pq
where rp.Id = pq.AuthorId
and AuthorsName = 'aristotle'
order by NEWID()

my question now is - this works perfectly fine with a single author. whats the best way to use the above code for many authors? would i need to repeat the above code for each author or is there a better way to do this? thanks again...

android.sm
Starting Member

36 Posts

Posted - 2010-07-04 : 18:45:32

ok fixed. thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-04 : 19:24:13

select quote
from (
select Quote, row_number() over (partition by pq.AuthorId order by newid()) AS recid
from PhilosophyAuthors pa
inner join PhilosophyQuote pq on pq.AuthorId = rp.Id
where AuthorsName = 'aristotle'
) AS d
where recid = 1

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

android.sm
Starting Member

36 Posts

Posted - 2010-07-10 : 14:24:01

how do i extend this so i can use other tables like, politics/religion?
i have tables like PoliticalAuthors, PoliticalQuote, ReligiousQuote, ReligiousAuthors etc. these tables are structured exactly like my PhiliosophyQuote & PhilosophyAuthors tables.

select top 1 Quote
from PhiliosophyQuote pq, PhilosophyAuthors pa
where pa.Id = @AuthorId
and pa.Id = pq.AuthorId
order by NEWID();

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources