Post reply

Query Help

  • February 14, 2007 at 12:46 pm

    #176772

    Hello,

    I am trying to generate a table in sql server 2000 that pulls x number of random assets per category from a table. 

    I have already generated a process that produces two tables.  One table has two fields: CATEGORY_NAME and ASSETS_NEEDED_FOR_ASSIGNMENT.  The other table is a list of all the assets I can choose from.  The field names are: ASSET_NUM, CATEGORY_NAME, RANDOM_NUM.

    My goal is to pull the top X number of random assets per category based on the random number.  I know that 2005 solves this issue, however, any ideas on how this can be accomplished with 2000?

    Kindest Regards,

  • February 14, 2007 at 12:55 pm

    #689804

    you typically use ORDER BY new_id() to randomize results:

    SELECT top 5 CATEGORY_NAME ,ASSETS_NEEDED_FOR_ASSIGNMENT FROM MYTABLE ORDER BY NEW_ID()

     

    run it multiple times, and you'll see the data is random.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 14, 2007 at 1:02 pm

    #689807

    Thank you Lowell

    Using today as an example, I need the top 1 randomly seeded asset in one category and the top 38 randomly seeded assets in another category.  Tommorrow those numbers will be different based on a new list of what's available.

    Kindest Regards,

  • February 15, 2007 at 11:15 am

    #690033

    I solved this by creating temp table that compares the number of assets available to the number of assets desired. 

    If the the number of available assets within a category is greater than the number desired then the record with the greatest random value assigned is deleted from the list of available assets.  This is monitored within the loop until the values match.

    Kindest Regards,

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply