Randomize query 1

[billybobk]

Is it possible to write a query that will pull one random record each time from a table? Here's an example of what I mean: Customers send us thank you's in appreciation for our product. We put the text of these thank you's into a table called "tblThanks" The record id is a guid (primary key, of course) and the field name we want to pull, lets call it "Testamonials". The contents of the field are something like "Love your product!" or "Great doing business!", "We worship DBA's as gods" etc. Each time the web page opens we want to display a different random quote from the table based on this query. Something like "select random testamonial from tblThanks". We can do the translation to ASP here. Thanks in advance!!

--Bill
One may not reach the dawn save by the path of the night
--Kahlil Gibran

 

[TheBugSlayer]

If you have (add) an identity column in your table this should be easier. All you will need to do is generate a pseudo ramdom number in the range of your first and last identity values and select a record which identity field value is equal to the number.

 

[billybobk]

BugSlayer: I thought of that and I wish that I could. But I cannot add any fields to the database. I MUST use what is there.

--Bill
One may not reach the dawn save by the path of the night
--Kahlil Gibran

 

[SQLSister]

Here's some code I have that does that.

select top 1 airportId, newid() from airport
order by newid()


This will randomly give me a different airport ID code each time I run it.

newid() is a function that creates unique values. Look it up in Books Online.

 

[Jamfool]

--@valuein is the number of records u want,@valueZ is the number of records to ignore starting from the first rec
--so if @valuein is 1 and valueZ is 1000, it will return 1 record starting from record 1001. So all you would have to do is randomise the @valueZ factor


DECLARE @a VARCHAR(8000)
DECLARE @valuein VARCHAR(5)
SELECT @valuein = 1
DECLARE @valueZ VARCHAR(5)
SELECT @valueZ = 1000
DECLARE @table_name VARCHAR(50)
SELECT @table_name = 'table_name'


DECLARE @ordered_column VARCHAR(50)
SELECT @ordered_column = 'description'


SET @a ='SELECT TOP '+@valuein+' '+@ordered_column+ ' FROM ' +@table_name+
' WHERE '+@ordered_column+' NOT IN (SELECT TOP '+@valueZ+' '+@ordered_column+' FROM '+@table_name+' ORDER BY '+@ordered_column+')ORDER BY '+@ordered_column

PRINT @a
EXEC(@a)

 

[Jamfool]

SQLSister , cool I learn something everyday

 

[billybobk]

Thanks! I'll try these later today.

--Bill
One may not reach the dawn save by the path of the night
--Kahlil Gibran

 

[billybobk]

Works like a charm Sqlsister. I gave you a star for that one. And Jamfool: I too learn something new every day from tek-tips.

--Bill
One may not reach the dawn save by the path of the night
--Kahlil Gibran

 

[TheBugSlayer]

And I too learn more than one thing everyday. Why is it not possible to just generate any type of number within a range in SQL Server, uh?

 

[TheBugSlayer]

Jamfool, this one's for you. I hope you'll read this or those who do will let you know. I haven't gotten a chance to test it but I have questions:

- How do you randomize @valueZ? In your example you are the one assigning the value.

If you order the resultset by @order_column then for @valuein = 1 you will you not always get the same record each time you run the query?

 

[Jamfool]

DECLARE @RandomNo int
DECLARE @Upper_Limit int
DECLARE @Lower_Limit int
SET @Upper_Limit = (SELECT COUNT(*) FROM TABLE_NAME)
SET @Lower_Limit = 1

SELECT @RandomNo = Round(((@Upper_Limit - @Lower_Limit -1) * Rand() + @Lower_Limit), 0)

PRINT @RandomNo

Something like this?

 

[Jamfool]

(dont need the -1 bit, changed to @lower_limit. Otherwise u wont get top value)