Last post Jul 23, 2014 04:27 AM by Dan_no7
Jul 22, 2014 12:49 PM|Dan_no7|LINK
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,
Jul 22, 2014 02:25 PM|PatriceSc|LINK
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()
UNION ALL SELECT * FROM
SELECT TOP 1 * FROM (SELECT TOP 2 * FROM @t ORDER BY Value) b ORDER BY NEWID()
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).
Jul 23, 2014 04:27 AM|Dan_no7|LINK
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?