locked
Randomized Algorithm and Sort RRS feed

  • Question

  • User-1487497583 posted

    Hi,

    I have a table which stores 10 records, each record contains a category (A, B, C or D) and a total value (decimal number). I would like to be able to create two lists each containing 5 records each.

    The lists should attempt to split the highest value records by category i.e. go through the list by category order first. If there are 4 cateogry A records and these are split with two relatively high values and two relatively low values then each list should have one high and one low value. I would however like there to be a random factor however so that each time the list is generated it is slightly different.

    I'm currently stuck on how to achieve this random factor (using C#) however while still preserving the list's value integrity and was just wondering if anyone knew a solution on how to solve this problem?

    Thanks in advance,

    Daniel

    Tuesday, July 22, 2014 12:49 PM

All replies

  • User753101303 posted

    Hi,

    So it seems you just want to split your subset ie rather than trying to take 2 rows from a random list you want to take 1 row from the list with the higher values and 1 row from the list with the lower values so that you can make sure that you have one row from each of those subset ?

    At least this is my understanding for now. It's also not crystal clear how it plays with categories (you want that for each category ? but then I'm not sure to see how you end up with 10 rows and 4 categories).

    But I likely not fully understand the detail (let's forget about categories for now ?)

    Perhaps if knowing what is the real world scenario ? ie showing high price books and low price books from several categories on a web page or whatever ???

    For example the proof of concept I was thinking about is something such as :

    DECLARE @t TABLE(Value REAL)
    INSERT INTO @t SELECT RAND() UNION ALL SELECT RAND() UNION ALL SELECT RAND() UNION ALL SELECT RAND()
    
    SELECT * FROM @t ORDER BY Value
    
    SELECT * FROM (
    	SELECT TOP 1 * FROM (SELECT TOP 2 * FROM @t ORDER BY Value DESC) a ORDER BY NEWID()
    ) a
    UNION ALL SELECT * FROM
    (
    	SELECT TOP 1 * FROM (SELECT TOP 2 * FROM @t ORDER BY Value) b ORDER BY NEWID()
    ) b
    ORDER BY Value
    
    

    That is basically I generate random 4 rows with random values and then:
    - I take the 2 highest values and take 1 of those randomly
    - I take the 2 lowest values and take 1 of those randomly

    So in the resulting union I'm sure that I have one row which is one of the 2 highest values as well as one row which is one of the 2 lowest values (if I understood what you want).

    Tuesday, July 22, 2014 2:25 PM
  • User-1487497583 posted

    You're right the real world example would make things a lot clearer.

    So basically I have a database table which stores sports team players who are categorised based on their position on the field. Each player has a total ability score. I would like to be able to select 10 players from this table. Then once the players have been selected for two teams of 5 to be created. Each team should be as evenly matched (based on ability) as possible and try and have a balance (i.e. similar amount of attackers and defenders on each team). There needs to be a random element involved in the selection process however so that the same teams aren't continually generated (whilst still maintaing as much as possible for each team to be even based on ability).

    Any ideas on how to achieve this?

    Thansk,

    Daniel

    Wednesday, July 23, 2014 4:27 AM