Randomly Getting a SQL Table

Results 1 to 3 of 3

Thread: Randomly Getting a SQL Table

  1. #1
    Join Date
    Dec 1969

    Default Randomly Getting a SQL Table

    Here&#039s my situation:<BR>On my website I have a side bar for advertising products. I want those products to be displayed randomly. Here is the info:<BR><BR>Table: Feature_Table<BR>Coumn names: Product_Name, Product_Type, ID<BR><BR>This is pretty much what the table looks like<BR><BR>Product_Name Product_Type ID<BR>Cold Fusion Enterprise Edition 4.0 Software 1<BR>Class Of Nukem&#039 High Movie 2<BR>Adobe Illustrator Gold Book 3<BR>3D Studio Max VQG Book 4<BR><BR>Now if I add a new Movie (the Matrix) It would have an ID of 5 and so on.<BR><BR>What I am currently doing is running a simple SQL statement: Select * from feature_table<BR>Then, in cold fusion I count the total number of rows.<BR>I use cold fusion again to create a random number between 1 and the total number of rows. I set that as a variable #random#<BR>Then, I run another SQL statement: Select * from feature_table where ID = #random#<BR><BR>Question: Rather then running all of that is there a way in SQL to get all of the info from a random row inside the table?<BR><BR><BR>Thank you,<BR><BR><BR>Gavin Myers<BR>gmyers@access-cash.com<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Randomly Getting a SQL Table

    SQL has the RAND([seed]) function, it returns a random float value between 0 and 1. Use it the same way you are doing in Cold Fusion, generate a number betwen 1 and total number of rows...<BR>It&#039d be good to write all in a stored procedure.<BR><BR><BR><BR>

  3. #3
    Garth Guest

    Default RE: Randomly Getting a SQL Table

    I ran into this problem last year. There is a way to get a random value, but the problem is your domain is so small you will not be pleased with the "randomnest*. In order for a random generator to produce decent random results the domain has to be large. For my project I simply sequentially accessed the records in the display table.<BR><BR>The project was really cool...take a look for yourself:<BR><BR>dart.fine-art.com<BR><BR>Refresh on this page 3 times and you will see that the "Featured Listings" are sequentially displayed.<BR><BR>Garth<BR>www.SQLBook.com<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts