How to find top 10 records from a table in Oracle, MySQL, and SQL Server? Examples

Finding top 10 records is common need for programmers working with tables and database. Be it courses, cars, cricket, players, etc. The list is never-ending. But how do select TOP records from the database? As this is what happens in the back-end. So, if you are also looking for the answer to this question, check out this article and know more. We will see how to select the TOP 10 records from different databases with practical examples.

How to find the Top 10 Records in a table

So, what's the wait? Let's jump into the wagon of selecting TOP records and see the results considering all the different databases.

1. SQL Server SELECT TOP 10 RECORDS

The SELECT TOP condition permits you to restrict the number of lines or level of columns returned in a question result set.

Since the order of tuples put away in a table is unknown, the SELECT TOP command is constantly utilized in conjunction with the ORDER BY clause. Consequently, the outcome set is restricted to the main N number of requested lines.

Syntax:

SELECT TOP (expression) [PERCENT]
[WITH TIES]
FROM
table_name
ORDER BY
column_name;

Following the TOP clause is an expression that determines the number of columns to be returned. The expression is assessed to float esteem on the off chance that PERCENT is utilized, else, it is switched over completely to BIGINT esteem.

The PERCENT keyword shows that the query returns the main N percentage of tuples, where N is the consequence of the expression. The WITH TIES permits you to return more lines with values that match the last column in the restricted outcome set. Note that WITH TIES might make a bigger number of lines be returned than you determine in the expression.

For instance, if you need to return the most costly items, you can utilize the TOP 1. Notwithstanding, in the event that at least two items have similar costs as the most costly item, then you miss the other most costly items in the outcome set. To keep away from this, you can utilize TOP 1 WITH TIES. It will incorporate the primary costly item as well as the subsequent one, etc.

Example:

Let the sample database schema be as follows:

1) Utilizing TOP with a constant

Query:

SELECT TOP 10
product_name, list_price
FROM
production.products
ORDER BY
list_price DESC;

Output:

2. Using TOP to return the first N percentage of Tuples

The accompanying model purposes PERCENT to determine the number of items returned in the outcome set. The production. products table has 321 lines, hence, one percent of 321 is a fraction value ( 3.21 ), SQL Server gathers it together to the following entire number which is four ( 4 ) for this situation.

Query:

SELECT TOP 1 PERCENT
product_name,
list_price
FROM
production.products
ORDER BY
list_price DESC;

Output:

3/ Using TOP WITH TIES to include tuples that match the values in the last tuple

Query:

SELECT TOP 3 WITH TIES
product_name,
list_price
FROM
production.products
ORDER BY
list_price DESC;

Output:

2. Oracle Database

In this model, the third costly item has a rundown cost of 6499.99. Since the assertion utilized TOP WITH TIES, it returned three additional items whose rundown costs are equivalent to the third one.

ORACLE

Syntax:

(1) For version Oracle 12:

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s)
FETCH FIRST number ROWS ONLY;

number ⇒ n (number) of rows needed in the output

(2) Older Oracle Version:

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

ROWNUM ⇒ row number, number ⇒ n (number) of rows needed in the output

Example:

Consider the following database:

(a) To get the top 3 rows

Query:

SELECT * FROM Customers
FETCH FIRST 3 ROWS ONLY;

Output:

(b) SQL statement to get the first 50% of the records from the "Customers" table

Query:

SELECT * FROM Customers
FETCH FIRST 4 PERCENT ROWS ONLY;

Output:

3. MySQL and PostgreSQL

Utilization of the Top N question implies you need to restrict the outcomes to a specific number of lines. These are utilized to get the best or latest columns from an outcome set.

Syntax:

SELECT column_name FROM table_name LIMIT N;

N → Number of tuples needed in the output

Example:

Consider the table "customer" as follows:

⇒To get the top 3 records from the table, the syntax is as follows:

Query:

SELECT * FROM customer LIMIT 3;

Output:

⇒We need to get the three most youthful clients and break down them for anticipating. You can see the accompanying code test.

Query:

SELECT * FROM customer ORDER BY customer_age ASC LIMIT 3;

Output:

The table information, first and foremost, will be requested regarding the customer_age. The most youthful client will be at the top, and the more established one will be at the base.

Then select the main 3 lines from the outcome set of the arranged information.

⇒Consider the possibility that you need to choose the main 4 rows beginning from offset 3.

Query:

SELECT * FROM you_table_name ORDER BY column_name ASC LIMIT offset,row_count;

Output:

Conclusion:

We have presumed that we can restrict the number of lines from an outcome set contingent upon the venture prerequisites. In MySQL/PSQL (PostgreSQL) we can choose the top N number of rows or the top N number of lines that begin from M as well as we discovered that the LIMIT provision could be utilized with/without ORDER BY.

In SQL Server we can use the TOP clause to get the top N tuples or top N percent of tuples as well as we can get the top N tuples with ties. Whereas in ORACLE DB we use the FETCH clause to get the required output.

Other SQL and Database Articles you may like:

• How to convert String to Date in SQL?
• 10 Examples of ALTER command in SQL
• How to use Stored Procedure in SQL?
• Difference between UNION and UNION ALL in SQL
• How to use WHERE and HAVING clause in SQL
• What are T-SQL Window Functions?
• 4 Examples of CASE expression in SQL Server
• Difference between Self and Equi join in SQL
• MySQL vs NoSQL comparison

Thanks for reading this article so far. If you have any questions or doubt feel free to ask.