You write the sql query below. add a from clause that will retrieve the data from the artist table.

The SQL SELECT TOP Clause

The SELECT TOP clause is used to specify the number of records to return.

The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.

Note: Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses FETCH FIRST n ROWS ONLY and ROWNUM.

SQL Server / MS Access Syntax:

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

MySQL Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

Oracle 12 Syntax:

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

Older Oracle Syntax:

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

Older Oracle Syntax (with ORDER BY):

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

Demo Database

Below is a selection from the "Customers" table in the Northwind sample database:

SQL TOP, LIMIT and FETCH FIRST Examples

The following SQL statement selects the first three records from the "Customers" table (for SQL Server/MS Access):

The following SQL statement shows the equivalent example for MySQL:

The following SQL statement shows the equivalent example for Oracle:

Example

SELECT * FROM Customers
FETCH FIRST 3 ROWS ONLY;

SQL TOP PERCENT Example

The following SQL statement selects the first 50% of the records from the "Customers" table (for SQL Server/MS Access):

The following SQL statement shows the equivalent example for Oracle:

Example

SELECT * FROM Customers
FETCH FIRST 50 PERCENT ROWS ONLY;

ADD a WHERE CLAUSE

The following SQL statement selects the first three records from the "Customers" table, where the country is "Germany" (for SQL Server/MS Access):

The following SQL statement shows the equivalent example for MySQL:

The following SQL statement shows the equivalent example for Oracle:

Example

SELECT * FROM Customers
WHERE Country='Germany'
FETCH FIRST 3 ROWS ONLY;

SQLSELECT statements are used to retrieve data from the database and also, they populate the result of the query into the result-sets. The SQL examples of this article discourse and explain the fundamental usage of the SELECT statement in the queries.

SQL (Structured Query Language) queries can be used to select, update and delete data from the database. If anyone desires to learn SQL, to learn the SELECT statements can be the best starting point. On the other hand, we can use T-SQL query language particularly for SQL Server databases and it is a proprietary extension form of the SQL.

SELECT statement overview

The most basic form of the SQL SELECT statement must be include SELECT, FROM clauses. In addition, if we want to filter the result set of the query, we should use the WHERE clause.

The above query template specifies a very basic SQL SELECT statement. As you can see, column names are placed after the SELECT clause and these columns are separated with a comma sign with (,). After the FROM clause, we add the table name in which we want to populate the data into the result set. In addition, the following query template illustrates the usage of the WHERE clause in the SELECT query.

With the WHERE clause, we can filter the result set of the select statement. Filtering patterns are used after the WHERE clause. Now, we will make some SQL examples of the SQL SELECT statement and reinforce these theoretical notions.

Basic SQL examples: Your first step into a SELECT statement

Assume that, we have a fruits table which likes the below and includes the following rows;

We want to get all data of the Fruit_Name from the Fruits table. In this case, we must write a SQL SELECT statement which looks like the below.SQL Server database engine processes this query and then returns the result-set of the query.

As you can see, the query returns only Fruit_Name column data.

Now, we will practice other SQL examples which are related to the SELECT statement. In this example first example, we will retrieve all columns of the table. If we want to return all columns of the table, we can use a (*) asterisk sign instead of writing whole columns of the table. Through the following query, we can return all columns of the table.

At the same time, to retrieve all columns, we can do this by writing them all separately. However, this will be a very cumbersome operation.

SQL examples: How to filter a SELECT statement

In this section, we will take a glance at simple clause usage of the WHERE clause. If we want to filter the result set of the SQL SELECT statement, we have to use the WHERE clause. For example, we want to filter the fruits whose colors are red. In order to filter results of the query, at first we add the column name which we want to filter and then specify the filtering condition. In the below SQL example, we will filter the red fruits of the Fruits table.

As you can see that, the result set only includes the red fruits data. However, in this example, we filter the exact values of the columns with (=) equal operator. In some circumstances, we want to compare the similarity of the filtered condition. LIKE clause and (%) percent sign operator combination helps us to overcome these type of issues. For example, we can filter the fruits who start with the letter “L” character. The following query will apply a filter to Fruit_Name and this filter enables to retrieve fruits who start with “L” chracter.

At the same time, we can apply (%) percentageoperator at any place or multiple times to thw filter pattern. In the following example, we will filter the fruits name which includes ‘n’ chracter.

Another commonly used operator is (_)the underscore operator. This operator represents any character in the filter pattern. Assume that, we want to apply a filter to the fruit names which meet the following criterias:

• The first character of the fruit name could be any character
• The second character of the fruit name must be ‘a’
• The remaining part of the fruit name can contain any character

The following SQL example will meet all criteria.

The SELECT TOP statement is used to limit the number of rows which returns the result of the query. For example, if want to retrieve only two rows from the table we can use the following query. Therefore, we can limit the result set of the query. In the following SQL examples, we will limit the result set of the query. Normally, the result of the query without TOP operator can returns much more rows but we force to limit returning row numbers of the query with TOP clause.

At the same time, we can limit the result set of the SQL SELECT statement with a percent value. Such as, the following query returns only %60 percentage of result set.

As you can see we added PERCENT expression to TOP operator and limit the result set of the query.

See also

For more articles on the SQL SELECT statement including SQL examples see

• SQL SELECT INTO Statement
• SQL INSERT INTO SELECT Statement Overview and Examples

• Author
• Recent Posts

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec

Which SQL clause is used to retrieve data FROM database?

What SQL clause can be used to retrieve data FROM multiple tables?

What SQL clauses is used to enter data into a SQL table?

What does the FROM clause in an SQL query do?