Problem solve Get help with specific problems with your technologies, process and projects.

The three smallest numbers

How do I find the three smallest numbers from a column of numbers?

Find the three smallest numbers from a column of numbers.

On several occasions, I have remarked on how much I really like short questions. Perhaps the person who asked this question knew this about me, but perhaps not.

Perhaps not, because this question sure looks like a homework assignment. The imperative Find is a dead giveaway.

There are two things I want to say about homework questions. First, I need to remind you that here at "Ask The Expert" we won't answer them. It's not ethical. Earn your marks by doing the work. You'll learn while you're doing, and that's the point.

The second thing I want to mention about homework questions is to encourage you not to give up. Sometimes SQL can be infuriatingly difficult. At those times, don't just post the question somewhere and see if it gets an answer. In the first place, it usually takes too long, especially here at "Ask The Expert" where the turnaround time is usually a week or three, and where the chances are, if it smells like homework, it will not be answered.

Don't post homework questions on Internet discussion forums either, because you're running the risk that you will actually get an answer, but the answer might have a high NZDF (Non-Zero Deviosity Factor©). An answer can be considered "devious" if it actually works but is either fatally flawed in some way, or uses some method which the student couldn't possibly have come up with alone, since it would indicate a mastery of the subject much greater than would be required to solve the simple question being answered.

Here's an example. Suppose you run this query:

select min(mycolumn) from mytable

This will give you the lowest value in the column. Write it down in case you forget it. Now use that value in the following query:

select min(mycolumn) from mytable
 where mycolumn <> value

Here value is the value you got from the first query. This second query will return the second lowest value. Write this down, too. Now run the following query:

select min(mycolumn) from mytable
 where mycolumn <> value1
   and mycolumn <> value2

Here, value1 and value2 are the values you wrote down from the first two queries. Write down what you got from this third query, et voilà, you will see that you have written down the three smallest numbers.

What's wrong with this solution? Well, it isn't exactly devious per se, but it's flawed. It does work, but it's clumsy and takes several steps. Would it be a good solution in the real world? Almost assuredly, assuming you only needed to do it once. Is it a good answer to hand in for a homework assignment? Well, you could try it, and see what happens.

But isn't there an elegant way to solve this problem using SQL? Yes, there is. This is why I encourage you not to give up. Keep trying.

One way to look at this question involves sorting the entire column. If you sort all the numbers in the column into ascending sequence, the smallest three numbers will come up first. This isn't the most elegant way to solve the problem, but it's practical. Now all you have to do is figure out how to sort a column of numbers into ascending sequence and take the top three values.

And that's as much of a hint as I'm willing to give for this homework assignment.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.