4 {{ upvoteCount | shortNum }}
4 {{ upvoteCount | shortNum }}

SQL - Choosing Random Records Depending on the date

by BenShaffer
3 replies
I have a table which has thousands of records.
What I want to do, is whenever the script is called show a random set of 20 records. However, for a whole day, I always want the same 20 random records to be shown.

I don't want to have to label those records or anything like that. Therefore I am assuming that the 20 records will be called depending on the date eg. 11/21/11, 11/22/1 etc.

I don't mind if certain of the table records are shown on more than one date. I also don't really mind if some of the records are never shown although ideally would like to avoid both of these things.

I am using ASP/SQL Server, although I am pretty sure that in any language the concept will be the same.

Can you guide me at all?

Thanks,

Ben Shaffer
#choosing #date #depending #random #records #sql
  • Profile picture of the author AdWordsUzmani
    I'm gonna explain this with my poor English.
    If I understood correctly, you want to do that


    1 - Check Your "temporary Field"

    2 - if it's empty
    2.1 - Call your script and get your random 20 records
    2.2 - Save 20 records ids' in your "temporary Field" eg. (1,25,54,13,54,64,24..)

    3 - if it's not empty
    3.1 - split those ids and get that records

    4 - Show records


    What is the "temporary Field"?
    Temporary Field can be txt file or xml file or table. It's up to you.
    But you need the storage that ids somewhere

    Also you need to check those ids with date!
    So, you can storage ids like this (11/21/11,1,25,54,13,54,64,24..)
    when you split them by "," your first item will be the date.
    {{ DiscussionBoard.errors[5100368].message }}
  • Profile picture of the author eminc
    Check out the queries with test results here.

    Carl J » Selecting Random Records With SQL

    I think the query for SQL server will be

    SELECT TOP 1 ProductID FROM Products WHERE ProductID <= 10 ORDER BY NEWID()

    Mohit
    Signature

    The best way to predict future is to create it ― Abraham Lincoln

    {{ DiscussionBoard.errors[5101930].message }}
  • Profile picture of the author BenShaffer
    Thanks guys.
    Hadn't thought of predoing the next xxxx days, but is an easier solution.
    Problem is if I add more records then they won't be included. I guess a solution to that is just to generate a new array every day rather than doing them in advance.
    Signature
    $100 a Day Using Automated Software

    Steal Amazon, Kindle and Buying Keywords In Seconds!
    {{ DiscussionBoard.errors[5105735].message }}

Trending Topics