Example: TOP 1, WHERE and ORDER BY

Question: How do I turn a user's guess at a value in a ordered sequence, into a value that actually exists?

For example, one of the new features in Foxhound Version 2 is the ability to "go to" a particular sample in the vast number of performance samples recorded in the database. The "go to" scroll value can be a datetime or a sample number, and it can be accurate or just a wild guess.

This question deals with a wild guess at the sample number. Here's a simplified example:

actual sample numbers in the database, in the same order that samples are displayed 
|
|    "go to" guess at the sample number
|     |
V     V

90
80
70
      65
60
50

Since sample number 65 doesn't exist, Foxhound scrolls to sample 70.

---

Why 70 and not 60? Because the action of "go to" is to scroll the chosen sample to the top of the display, and when the display scrolls to 70, samples 60 and 50 are visible below it. If Foxhound scrolled to sample 60 then 70 wouldn't be visible.

That's how the decision was made. There is no truth to the rumors about dice and coin tosses :)

---

Answer: The TOP 1, WHERE and ORDER BY clauses of the SELECT statement make this kind of query easy to code, and quite often very efficient as well.

Here's the actual code:

SELECT TOP 1 rroad_sample_set.sample_set_number
  INTO @history_goto_sample_set_number
  FROM rroad_sample_set
 WHERE rroad_sample_set.sampling_id       = @sampling_id
   AND rroad_sample_set.sample_set_number >= @candidate_history_goto_sample_set_number
 ORDER BY rroad_sample_set.sample_set_number ASC;

Here's how the TOP 1, WHERE and ORDER BY clauses work together to answer the question:

• The WHERE ... AND predicate on line 5 limits the result set to sample numbers 70, 80 and 90... all of them are >= 65.
  

• The ORDER BY ... ASC on line 6 sorts the result set so that 70 comes first.
  

• The TOP 1 clause picks 70 as the final answer.
  

• The input to this query is the local variable @candidate_history_goto_sample_set_number = 65, and the output is @history_goto_sample_set_number = 70.
  

• The other predicate (the WHERE ... = @sampling_id on line 4) deals with the fact that Foxhound samples are partitioned by the target database being monitored.
  

It's perfect! What could possible go wrong?

Getting the ORDER BY wrong is a common mistake, but it's easy to fix: "ooops, that should be ASC... I think... let's try it and see."

Getting the WHERE predicate wrong is another common mistake: "should that be >= or just > ? ...or should it be < ?" ...not so easy to "just try it and see", better to think about it a bit.

In these cases "common mistake" is not an exaggeration, "almost always" is closer to the truth... if you're not an Alpha.

But, it's a valuable technique, useful in a whole variety of situations, and they all have TOP 1, WHERE and ORDER BY... you just have to pick the right WHERE operator and the right ORDER BY direction.

And if you start by scribbling down an example with data values, you can get the code right first time.

Sometimes :)

---