Back to articles list May 15, 2017 - 6 minutes read Oracle Top-N Queries for Absolute Beginners Aldo Zelen Aldo is a data architect with a passion for the cloud. From leading a team of data professionals to coding a data warehouse in the cloud, Aldo has experience with the whole lifecycle of data-intensive projects. Aldo spends his free time forecasting geopolitical events in forecasting tournaments. Tags: FETCH oracle top queries It's common to run a query using only part of a dataset – for example, the top 100 salespeople in a company. In this article, we'll see how to use Oracle's Top-N query method to query just these rows. Top-N queries retrieve a defined number of rows (top or bottom) from a result set. In other words, they find the best or worst of something – the ten best selling cars in a certain region, the five most popular routers, the 20 worst-performing stores, etc. These kinds of queries are mostly used in Business Intelligence, where it's important to track the performance of certain entities and thus find ways of increasing profits or cutting costs. As database developer or BI developer, your role is to enable users to get results in a timely and dependable manner. In this article, we will explore some common ways of constructing Top-N queries in an Oracle database. Get Set, Get Ready First, we need to lay the groundwork for our queries by familiarizing ourselves with some basic analytical functions. If you aren't familiar with SQL functions, I recommend using Vertabelo Academy, particularly the Standard SQL Functions Course. Assuming you do have some background in SQL, let's get started by looking at the data in a simple sales table: This table contains the following information: id – A unique customer identifier in the system. account – The customer's account number. basket – Identifies the product group that was sold. amount – The total number of products purchased. sales date – The date of the sale. Now let's query this table and see what we have inside: SELECT * FROM sales ORDER BY amount DESC; Here is the result: Now suppose you want to query the top five sales, or the five biggest sales. Usually, people execute this query using the Oracle pseudocolumn ROWNUM. ROWNUM returns a number indicating the order that the row is selected from the table. Here's the code: SELECT * FROM sales WHERE rownum <= 5 ORDER BY amount DESC; But this is a mistake, as you can see from the result set: This is because ROWNUM is executed before the ORDER BY statement. To get around this, you use the inline view (a SELECT statement within the FROM clause of another SELECT, as shown below.) This tells Oracle to sort the data first. select * from (select * from sales order by amount desc) where rownum <= 5; And we get the correct result: However, there is one problem: how we define the best sale. Are the five best sales the first five distinct best? The five non-distinct best? Or the first five ranked sales? The definition of the best sales will come from the business side of things, but figuring out the solution is up to you. What Is a Top-N Query? Now that we understand the business problems and the data, let's move on to Top-N queries. I'll look at each of these query types: Top-N with ROW_NUMBER Top-N Distinct Top-N with RANK Top-N queries don't have a special command or keyword. They utilize an ordered inline view, select results from that view, and then limit the number of rows using ROWNUM. Note for Oracle 12c users: Oracle 12c introduced a new clause, FETCH FIRST. This allows us to write Top-N queries by adding the FETCH FIRST clause at the end of a basic query. I've included examples of this method as well. Top-N with ROW_NUMBER This is the classic Top-N. It is non-distinct, meaning that there may be gaps in the ranking if two or more values are the same. The ROW_NUMBER analytical function returns a unique row for each returned row. We can take this query further than we can one with ROWNUM because of the windowing aspect of ROW_NUMBER. To learn more about analytical functions in Oracle, start here. The query is : SELECT * FROM ( SELECT sales.*, ROW_NUMBER() OVER (ORDER BY amount DESC) AS amount_dense_rank FROM sales ) WHERE amount_dense_rank <= 3; And the result we get is: Oracle 12c Syntax To get the same result using Oracle 12c, we simply write: SELECT amount FROM sales ORDER BY amount DESC FETCH FIRST 3 ROWS ONLY; And the result we get is: Top-N Distinct To get a Top-N with distinct results (i.e. no gaps in the ranked results), we use the DENSE_RANK analytical function. DENSE_RANK is similar to RANK (which we will discuss below), but it returns without any gaps in the results. We would use the Top-N Distinct query type when we want to see all the values and all the corresponding rows. Here is an example of a Top-N query with DENSE_RANK: SELECT * FROM ( SELECT sales.*, DENSE_RANK() OVER (ORDER BY amount DESC) AS amount_dense_rank FROM sales ) WHERE amount_dense_rank <= 3; And the result: We see that the same amounts are given the same rank – there are multiple 1s, 2s, and 3s – but there are no gaps. We will receive the three highest distinct values. Top-N Non-Distinct with Ranked Values A Top-N non-distinct query with ranked values will return an N number of the highest values and all the corresponding rows. To implement this, we use the RANK analytical function, which produces a sequential rank for each distinct value in the specified window. Let's look at an example with three amounts: SELECT * FROM ( SELECT sales.*, RANK() OVER (ORDER BY amount DESC) AS amount_rank FROM sales ) WHERE amount_rank <= 3; And the results : The RANK function returns the same number for non-distinct values and skips the number of distinct values to keep the rank consistent. If we had three sales that all had amounts of 100, there would be three "first-place" values with the amount of 100 and the next rank would be four. Oracle 12c Syntax You can achieve the same ranking results using Oracle 12c's new syntax feature: SELECT amount FROM sales ORDER BY amount DESC FETCH FIRST 3 ROWS WITH TIES; And we get the same result: We've explored three simple methods of creating Top-N queries with analytical functions. And we've explored a new syntax available in the Oracle 12c database. While we've concentrated on finding the best performers, an inverse query that finds the worst performers is also possible. All you'd need to do is reverse the order of the analytical functions or inline views. Why not put in some practice with creating queries? I recommend the SQL Window Functions online course – the only interactive course for SQL analytical functions on the Internet. See what you can come up with! Tags: FETCH oracle top queries You may also like SQL Filtering 101 Sometimes, SQL queries return lots of data you don't need. Look at a simple example of filtering SQL queries to reduce the complexity of your data. Read more Learning JOINs With Real World SQL Examples JOIN statement lets you work with data stored in multiple tables. This article is a practical introduction to the SQL JOIN. Check out examples in real life. Read more SQL INSERT, SQL UPDATE, SQL DELETE – Oh My! Need to understand SQL INSERT, UPDATE, or DELETE? We'll get you up to speed with a comprehensive How-To and useful examples! Read more Useful SQL Patterns: Conditional Summarization with CASE When you are using SQL aggregate functions to create report queries, you'll frequently find yourself using conditional summarization with the CASE operator. Read more Refine Results with SQL Set Operators The function of SQL set operators is pretty simple; they allow us to combine results from different SQL queries into one result set. Read more The Most Important SQL Queries for Beginners This guide discusses the 30 most important SQL queries for beginners. Let's take a look. Read more The Complete Beginner’s Guide to SQL Fundamentals Find out what SQL and databases are, and how they work together. In this article, you'll find a complete guide to SQL fundamentals. Read more Oracle Top-N Queries for Absolute Beginners Top-N queries retrieve a defined number of rows from a result set, i.e. 10 best-selling cars, 5 most popular routers, 20 worst-performing stores, etc. Read more How to Sort Records with the ORDER BY Clause We'll take a closer look at the ORDER BY clause in SQL – how to write it, how it works, and what it does to a relational database. Read more SQL Hacks To Control Family Budget On Black Friday Weekend Black Friday is just getting started but not everyone has already developed money management skills. Here’s how SQL could help you control family finances. Read more How to Start Thinking in SQL If you want to know how to write better SQL queries, change the way you think. Instead of using the language, start thinking in SQL. Read more Essential SQL Terms to Know for Beginners and Pros In this article, we’ll explore some basic SQL database terminology you need to know to succeed in the data analysis world. Read more How to Begin Running SQL Queries What do you need to run SQL queries? Jump right in to find out! We'll assist you in writing and executing your first SQL query. Read more SQL Order of Operations Is the order in which SQL operations are executed important? Improve your SQL skills by learning the order of operations execution in SQL. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.