Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 3rd, 2006, 12:49 PM
Authorized User
 
Join Date: Jun 2005
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default Display a random record for 24 hours

Hello Experts,

I am using following SQL query to select a random record for my website:

SELECT TOP 1 *
FROM #TEMP
ORDER BY NewID()

Now I want that this query selects one random record every 24 hours and then that random record is displayed on my website for 24 hours and then record is changed. Any suggestions on how can I accomplish this?

Thanks in advance,
Palvin

 
Old February 3rd, 2006, 02:43 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

What server side technology are you using for your web site?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old February 3rd, 2006, 04:25 PM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

You can schedule a DTS package to run that code and place the row into a table. Then in your web app pull that row. The next time, truncate the table and insert a new row...etc.

Jim

 
Old February 3rd, 2006, 04:59 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Here is a simple table and some code.

Code:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[RandomList]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE RandomList

CREATE TABLE RandomList (
    ID integer IDENTITY
    , Randomizer integer
    , Pointer varchar(10)
)
GO

CREATE PROC ReRandomize
AS
TRUNCATE TABLE RandomList

INSERT RandomList (Randomizer, Pointer)
VALUES ( CAST ((Rand() * 1000000000.0) AS integer), 'One')

INSERT RandomList (Randomizer, Pointer)
VALUES ( CAST ((Rand() * 1000000000.0) AS integer), 'Two')

INSERT RandomList (Randomizer, Pointer)
VALUES ( CAST ((Rand() * 1000000000.0) AS integer), 'Three')

INSERT RandomList (Randomizer, Pointer)
VALUES ( CAST ((Rand() * 1000000000.0) AS integer), 'Four')

INSERT RandomList (Randomizer, Pointer)
VALUES ( CAST ((Rand() * 1000000000.0) AS integer), 'Five')

INSERT RandomList (Randomizer, Pointer)
VALUES ( CAST ((Rand() * 1000000000.0) AS integer), 'Six')

INSERT RandomList (Randomizer, Pointer)
VALUES ( CAST ((Rand() * 1000000000.0) AS integer), 'Seven')

INSERT RandomList (Randomizer, Pointer)
VALUES ( CAST ((Rand() * 1000000000.0) AS integer), 'Eight')

INSERT RandomList (Randomizer, Pointer)
VALUES ( CAST ((Rand() * 1000000000.0) AS integer), 'Nine')

INSERT RandomList (Randomizer, Pointer)
VALUES ( CAST ((Rand() * 1000000000.0) AS integer), 'Ten')
GO

-- Execute this whenever you wish to re randomize the table
EXEC ReRandomize

SELECT * FROM RandomList

SELECT * FROM RandomList ORDER BY Randomizer

-- Execute this to select one random record from the table
SELECT TOP 1 * FROM RandomList ORDER BY Randomizer
Yes my name really is Rand.


Rand
 
Old February 3rd, 2006, 05:07 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You could also use a cursor to scroll through the table and UPDATE the Randomizer field whenever you wanted a new "random" selection.

Rand
 
Old February 3rd, 2006, 06:13 PM
Authorized User
 
Join Date: Jun 2005
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello All,

Thanks for your responses. I am using ASP vbscript on my website.

I will follow these guidelines and will let you all know how I made it.

Once again I really appreciate all your support.

Thanks

 
Old February 6th, 2006, 10:36 AM
Authorized User
 
Join Date: Jun 2005
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello Rand,

Thanks, It worked for me and now I am getting a random record. I really appreciate all you guys' support.

Thanks






Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Run a Windows Service every 24 hours srkarthik_82 General .NET 5 January 10th, 2007 09:29 PM
Flagging a Random Record ptleitch SQL Server ASP 0 April 3rd, 2006 09:58 AM
display random record from access database jialin PHP Databases 1 December 2nd, 2005 09:18 PM
Random Table/Field/Record Ravenwillow VB Databases Basics 10 August 18th, 2005 08:10 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.