Determine the closest numbers to a specified number

[Guest]

Hi. I have a datatable containing a column named 'Price'. The datatable is
populated with records of varying prices ($1 - $50,000). How can I select
one record from this datatable, and then determine the 5 rows that are
closest to this record's 'Price' value?

For example - Let's say the following items are rows from this datatable.
The value shown is the 'Price' column's value for each respective row. How
could I progamatically say "Give me the 5 closest rows to row# 2's value
(10), and in return receive 7, 20, 100, 1000, 2000?

- 7
- 10
- 20
- 100
- 1000
- 2000
- 3000
- 4000
- 5000
- 6000
- 7000

Thanks

 

[Guest]

Hi. I have a datatable containing a column named 'Price'. The datatable is
populated with records of varying prices ($1 - $50,000). How can I select
one record from this datatable, and then determine the 5 rows that are
closest to this record's 'Price' value?

For example - Let's say the following items are rows from this datatable.
The value shown is the 'Price' column's value for each respective row. How
could I progamatically say "Give me the 5 closest rows to row# 2's value
(10), and in return receive 7, 20, 100, 1000, 2000?

- 7
- 10
- 20
- 100
- 1000
- 2000
- 3000
- 4000
- 5000
- 6000
- 7000

Thanks

The absolute difference between two values is abs(value1 - value2).
Order by the absolute difference between the price and the selected
value, and select the top five records where the price is different from
the selected value.

 

[Peter Duniho]

Hi. I have a datatable containing a column named 'Price'. The datatable is
populated with records of varying prices ($1 - $50,000). How can I select
one record from this datatable, and then determine the 5 rows that are
closest to this record's 'Price' value?

Can you retrieve the original data rows in order, sorted according to price?

If so, then an efficient solution would be to track the price
difference for rows both above and below the row of interest.
Comparing the price difference and moving the next row in the direction
where the price difference remains the least will allow you to
determine the five nearest rows without having to manipulate the entire
data set (beyond what was required to get it in order in the first
place).

While sorting the entire data set, either based on actual price or
price difference, is an elegant solution, if the data's not already in
some usable order it would be more efficient to simply scan the data
set once, tracking the five closest values.

As an optimization, you can keep a sorted list of the current five
candidates (sorted by their price difference from the target row) so
that the scan goes more quickly.

On average, keeping that list sorted as you scan the data set would be
much less expensive than sorting the whole data set, since even an
insertion sort on an array would be dirt cheap and only executed when a
row you're looking at is closer to the target row than at least one of
the current top five (something that can be determined with a single
comparison).

But even without that optimization, the O(n log n) cost of sorting the
data would still exceed the 5 * O(n) cost of a linear scan for any data
set larger than 32 rows. I think a linear scan is the way to go, one
way or the other.

Pete