# How to select two values, one greater than and one less than a given number

• ### 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

• 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.

Thursday, December 25, 2014 6:49 AM

### 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.

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.

Thursday, December 25, 2014 6:49 AM
• 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 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.