Display a random record for 24 hours

palvin · February 3rd, 2006, 12:49 PM

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

Imar · February 3rd, 2006, 02:43 PM

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

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

jbenson001 · February 3rd, 2006, 04:25 PM

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

rgerald · February 3rd, 2006, 04:59 PM

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

rgerald · February 3rd, 2006, 05:07 PM

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

Rand

palvin · February 3rd, 2006, 06:13 PM

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

palvin · February 6th, 2006, 10:36 AM

Hello Rand,

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

Thanks