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

#### Start the conversation

Send me notifications when other members comment.

## SearchDataManagement

• ### Oracle Data Science efforts advance with new services

Oracle adds news new services to its cloud infrastructure platform in a bid to provide data scientists, as well as data analysts,...

• ### Data governance metrics: Data quality, data literacy and more

Statistics on data quality are the most common metrics used to track data governance programs, but other types of data-related ...

• ### Snowflake raises \$479 million to boost cloud data warehouse

Snowflake is looking to grow its data warehouse platform with new funding and a partnership with Salesforce that could ...

## SearchBusinessAnalytics

• ### MicroStrategy analytics suite fosters insurance co-op's growth

Using MicroStrategy's BI platform, a Canada-based insurance co-operative has been able to spread analytics to end users ...

• ### Yellowbrick Data, MicroStrategy partnership aims to enhance analytics

The new partnership will enable flexible scalability, support for mixed workloads and multi-cloud support. The goal is to provide...

• ### Ethical data collection in the spotlight over new regulations

New regulations have put data privacy top of mind for many consumers. Here's a look at how businesses can incorporate ethical ...

## SearchSAP

• ### Take care of data before SAP S/4HANA migration

In this Q&A, Rajesh Rengarethinam of ERP security vendor Appsian discusses why reviewing data security and business processes are...

• ### Deadline extended for SAP Business Suite 7

Business Suite 7 will receive mainstream maintenance until 2027, a decision industry observers say reflects the difficulty of an ...

• ### SAP cloud revenue up, S/4HANA adoption still slow

Although SAP reported impressive revenue and cloud growth for 2019, it remains challenged to get the majority of its installed ...

## SearchSQLServer

• ### SQL Server database design best practices and tips for DBAs

Good database design is a must to meet processing needs in SQL Server systems. In a webinar, consultant Koen Verbeeck offered ...

• ### SQL Server in Azure database choices and what they offer users

SQL Server databases can be moved to the Azure cloud in several different ways. Here's what you'll get from each of the options ...

• ### Using a LEFT OUTER JOIN vs. RIGHT OUTER JOIN in SQL

In this book excerpt, you'll learn LEFT OUTER JOIN vs. RIGHT OUTER JOIN techniques and find various examples for creating SQL ...

## TheServerSide.com

• ### Why the 8 Java primitive data types are not objects

What's the difference between Java primitive types and objects? To start, don't classify primitive types as objects. Let's ...

• ### A brief history of Java: How it forever changed programming

The Java programming language is an important tool in an application developer's quiver. But Java has also undergone many changes...

• ### How to properly perform Java String comparisons

Don't let incorrect Java String object comparisons bog down your code. Follow this tutorial to understand the differences between...

## SearchDataCenter

• ### Initiative aims to improve data center incident reporting

The Data Center Incident Reporting Network hopes to pull back the smoke screen on software and hardware issues to improve ...

• ### A guide to GPU implementation and activation

Data center GPU usage goes beyond hardware. Admins must work with vendors and developers to have the right software architecture ...

• ### Key components for negotiating an HPC colocation contract

When it comes to HPC colocation, there are specialized areas to cover in a service-level agreement. Review provider protocols for...

## SearchContentManagement

• ### Akeneo rolls out newest version of PIM software

Akeneo PIM version 4.0 features digital asset manager, API-integrated connection modules and AI attribute mapping to better ...

• ### Get to know 4 workflow automation tools

Workflow automation can benefit businesses by making manual processes digital, giving employees more time to work on other tasks....

• ### Decide if paywall content is right for your business

Organizations that sell content might benefit from a paywall. Here are some questions to ask when deciding if a paywall strategy ...

## SearchHRSoftware

• ### 8 HR technology trends CHROs should investigate in 2020

Many emerging HR technologies are solidly in the realm of 'must-haves,' while others remain hype. Here's some advice to help you ...

• ### How to attract top talent in a competitive hiring market

Finding the best candidates can seem impossible in today's competitive hiring landscape. Use these methods to help you attract ...

• ### Federal workforce too reliant on degrees, says White House

In its budget proposal, the Trump administration complains that hiring rules hurt its ability to hire those just out of college ...

Close