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は無料でオープンソースのデータベースGUIです
今まで使った中で最高のSQLクエリ＆エディタツールです。データベース管理に必要なすべてが揃っています。 - ⭐⭐⭐⭐⭐ Mit
Beekeeper Studioは高速で直感的、使いやすいです。Beekeeperは多くのデータベースをサポートし、Windows、Mac、Linuxで快適に動作します。
Beekeeper Studioについてユーザーの声
"Beekeeper Studioは私の古いSQLワークフローを完全に置き換えました。高速で直感的で、データベース作業を再び楽しくしてくれます。"
"多くのデータベースGUIを試しましたが、Beekeeperは機能とシンプルさの完璧なバランスを実現しています。とにかく動きます。"