How to get item from a list that position in a certain place-CodePudding

Please consider this scenario:

For some math calculations I should find a number in specific place in a sorted list. For example consider this list:

1 - 2 - 3 - ... 17 - 18 - 19 - 20

I should to find number placed in 25% of count (count / 4). In above series I should get 5. It is worth noting that we haven't round count number but it's not a problem.

Now consider this table:

Type            Number
----------------------
  1               10
  1               11
  1               12
  1               13
  2               22
  2               23
  2               24
  2               25
  2               26
  2               27
  2               28
  3               39
  3               38
  3               37
  3               36
  3               35
  3               34
  3               33
  3               32
  4               41
  4               43
  4               42
  4               44
  4               45
  4               47
  4               46
  4               48
  4               49
  4               50
  4               51

Another point is I'm sure that in every Type I have at least 1000 numbers, so above data in just for example.

according to above data I want to get this result:

Type            Number
----------------------
  1               11
  2               23
  3               33
  4               43

One way to achieve this result is to loop throw distinct Type and get list of number and then sort it and then calculate count of that list and divide it by 4, then round the result and get specific Number with the index has been gotten.

But the problem with this approach is it needs many connection to database (1 for each Type). Is there any better solution to get desired result with 1 connection and 1 query execution. thanks

CodePudding user response：

Interesting puzzle. In Sql Server you could use something like the following query;

select a.*
from (
    select *, row_number() over(partition by type order by number) as row_number
    from table_name
) a
join (
    select type, count(*) as count
    from table_name
    group by type
) b on a.type = b.type
where a.row_number = b.count/4

(With whatever rounding you want for when count%4 != 0)

But I can't think how you would build that as a linq expression.

CodePudding user response：

 var percent = 0.25;
 var val = res.GroupBy(x => x.type)
              .ToDictionary(x => x.Key, x => x.OrderBy(y=>y).ToList());
 var valuesTobeTaken = val.Select(x => new 
                              { 
                                 x.Key, 
                                 index = ((int)Math.Round(x.Value.Count * percent))-1 
                               });

Edge cases are not handled and the code is not too much optimized. You can work on that i guess

        foreach (var rec in valuesTobeTaken)
        {
            Console.WriteLine(val[rec.Key][rec.index]);
        }