none
How to select two values, one greater than and one less than a given number RRS feed

  • Question

  • I have got a table, one of the column is of type decimal. During the runtime I'll get a number which is also decimal. Now I need to select two field one for each row which is based on the number.

    For example: The column has values

    1.001

    1.002

    1.003

    1.004

    The number is 1.0034, which is between 1.003 and 1.004. Is there a way to select these two rows?

    What I have done now is

                   if (tMinuscByDo >= 0.002 & tMinuscByDo <= 0.005)
                   {
                       tMinusC1 = 0.002M;
                       tMinusC2 = 0.005M;
                   }
                   else if (tMinuscByDo >= 0.005 & tMinuscByDo <= 0.01)
                   {
                       tMinusC1 = 0.005M;
                       tMinusC2 = 0.01M;
                   }
                   else if (tMinuscByDo >= 0.01 & tMinuscByDo <= 0.02)
                   {
                       tMinusC1 = 0.01M;
                       tMinusC2 = 0.02M;
                   }
                   else if (tMinuscByDo >= 0.02 & tMinuscByDo <= 0.04)
                   {
                       tMinusC1 = 0.02M;
                       tMinusC2 = 0.04M;
                   }
                   else if (tMinuscByDo >= 0.04)
                   {
                       tMinusC1 = 0.04M;
                   }
                   var tableName1 = ctx.ListOfTables.Where(t => t.DNVTables.Contains("tMinusC") && t.Values.Value == tMinusC1).Select(t =>t.DNVTables).FirstOrDefault();
                   var tableName2 = ctx.ListOfTables.Where(t => t.DNVTables.Contains("tMinusC") && t.Values.Value == tMinusC2).Select(t =>t.DNVTables).FirstOrDefault();

    This method is not feasible for large tables and for the tables that I don't know the exact values. All I need is two rows, where the given number lies in between.

    I am using entity framework 6 for data access.

    Wednesday, December 24, 2014 8:02 PM

Answers

  • Hello Bopy,

    One way to implement this requirement is to split the query to two parts, one is to find all rows whose decimal type column is less than the runtime number you got and get the single row whose value is max, the second part is to find all rows whose value is bigger than the runtime one, and get the min row. Then using the Union() method to contact the result, here is an example:

    using (DFDBEntities db = new DFDBEntities())
    
                    {
    
                        decimal number = Convert.ToDecimal(1.0034);
    
                        var result = (from od1 in db.OrderDetails
    
                                      where od1.Count <= number
    
                                      orderby od1.Count descending
    
                                      select od1).Take(1).Union(
    
                                      (
    
                                      from od2 in db.OrderDetails
    
                                      where od2.Count >= number
    
                                      orderby od2.Count
    
                                      select od2).Take(1)).ToList();
    
                    }
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 25, 2014 6:49 AM
    Moderator

All replies

  • Hello Bopy,

    The case seems more related to EF forum, I will move this case to EF forum.

    Best regards,



    Barry
    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 25, 2014 5:51 AM
  • Hello Bopy,

    One way to implement this requirement is to split the query to two parts, one is to find all rows whose decimal type column is less than the runtime number you got and get the single row whose value is max, the second part is to find all rows whose value is bigger than the runtime one, and get the min row. Then using the Union() method to contact the result, here is an example:

    using (DFDBEntities db = new DFDBEntities())
    
                    {
    
                        decimal number = Convert.ToDecimal(1.0034);
    
                        var result = (from od1 in db.OrderDetails
    
                                      where od1.Count <= number
    
                                      orderby od1.Count descending
    
                                      select od1).Take(1).Union(
    
                                      (
    
                                      from od2 in db.OrderDetails
    
                                      where od2.Count >= number
    
                                      orderby od2.Count
    
                                      select od2).Take(1)).ToList();
    
                    }
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 25, 2014 6:49 AM
    Moderator
  • Ado.Net means you need to query this two numbers?

    If it right then two selects will give you needed numbers:

    --higher value
    select top 1 table.numbers from table
    where table.numbers >= @Input_number
    order by table.numbers;
    
    --lower value
    select top 1 table.numbers from table
    where table.numbers <= @Input_number
    order by table.numbers;


    • Edited by xjomanx Thursday, December 25, 2014 7:40 AM
    Thursday, December 25, 2014 7:39 AM
  • Thanks Barry. I think it would be better if we could tag the name of the forums so that more people could see the question.
    Thursday, December 25, 2014 9:59 AM
  • @Bopy,

    Good suggestion, we can raise this issue under this forum:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=reportabug

    However, since the forum now do not have a specific tag, I will recommend you take notice of the following tags:

    Use View All we can choose a forum by click the right part to expand and select a forum.

    Use left part to see selected forums for example I only selected ADO .NET forum so that left part I only have one tag. I agree that this kind of tag looks not so clear for us.

    Best regards, 



    Barry
    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Barry Wang Friday, December 26, 2014 3:24 AM
    Friday, December 26, 2014 3:22 AM