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:
Beekeeper Studio is the SQL editor and database manager of your dreams
Tried a few tools. Beekeeper was the only one that I found that felt right. Most had a very 1990's feel to them - Allan
I built Beekeeper Studio because, like Allan, I wanted something more intuitive and modern than all the existing clunky apps I could find. My customers agree - they love using Beekeeper and they tell me every day! Give it a try, I bet you'll like it too.
