How to Limit Rows in Teradata

Limiting Rows in Teradata Using the TOP Keyword

The TOP keyword is the most straightforward way to limit rows in Teradata. It specifies the exact number of rows to return.

Syntax:

SELECT TOP n column1, column2, ...
FROM table_name;

Example:

Suppose you have a table called employees and you want to retrieve the top 10 rows.

SELECT TOP 10 employee_id, first_name, last_name
FROM employees;

Expected Output:

 employee_id | first_name | last_name
-------------|------------|-----------
 1           | John       | Doe
 2           | Jane       | Smith
 ...         | ...        | ...
 10          | Alice      | Brown

Using the SAMPLE Keyword

Another way to limit rows in Teradata is through the SAMPLE keyword. This is particularly useful when you need a random sample rather than the top rows.

Syntax:

SELECT column1, column2, ...
FROM table_name
SAMPLE n;

Example:

To retrieve a random sample of 5 rows from the employees table:

SELECT employee_id, first_name, last_name
FROM employees
SAMPLE 5;

Expected Output:

 employee_id | first_name | last_name
-------------|------------|-----------
 7           | Emma       | Johnson
 3           | Robert     | Lee
 ...         | ...        | ...
 12          | Olivia     | Davis

Using SAMPLE with a Percentage

The SAMPLE clause also supports limiting rows by a percentage.

Syntax:

SELECT column1, column2, ...
FROM table_name
SAMPLE n PERCENT;

Example:

Retrieve a 10% sample of rows from the employees table:

SELECT employee_id, first_name, last_name
FROM employees
SAMPLE 10 PERCENT;

Expected Output:

 employee_id | first_name | last_name
-------------|------------|-----------
 2           | Jane       | Smith
 9           | Mark       | Wilson
 ...         | ...        | ...
 22          | Lucas      | Adams

Using QUALIFY with ROW_NUMBER()

For more complex scenarios, you can use the QUALIFY clause with ROW_NUMBER(). This method allows you to apply additional filters and conditions.

Syntax:

SELECT column1, column2, ...
FROM table_name
QUALIFY ROW_NUMBER() OVER (PARTITION BY partition_column ORDER BY sort_column) <= n;

Example:

Retrieve the top 5 highest-paid employees in each department:

SELECT department_id, employee_id, salary
FROM employees
QUALIFY ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) <= 5;

Expected Output:

 department_id | employee_id | salary
---------------|-------------|--------
 1             | 101         | 120000
 1             | 102         | 115000
 ...           | ...         | ...
 2             | 201         | 95000
 ...           | ...         | ...

Conclusion

Limiting rows in Teradata can be achieved using various methods, each suitable for different scenarios. The TOP keyword is straightforward for simple limits, while SAMPLE provides randomness and percentage-based sampling. For more complex requirements, ROW_NUMBER() with QUALIFY offers powerful row limitation based on specific conditions. Understanding these techniques will help you manage large datasets more efficiently and effectively in Teradata.

Other articles you may enjoy:

• How to Add a Column in Teradata
• Teradata Corporation (TDC) Revenue, 2024 Q1