Quick Tip: Limit The Number Of Rows Returned From A SQL Query

This is pretty basic, but I had to look up the syntax to use for SQL on IBM DB2 so I thought I’d write a quick tip.

Limiting the number of records returned from a query is really helpful when querying a table with millions of records and you expect a large amount of data back. I use it a lot when determining what my ‘where’ statement should look like. My workflow usually goes something like this:

1. I know the data is in table F0911 (yeah, it’s a big one)
2. I only need records associated with a particular Document Number but I don’t remember what that column is named.
3. So, I start with this:
   SELECT * FROM PRODDTA.F0911 FETCH FIRST 10 ROW ONLY
4. That way I get the column names and can continue working on the ‘WHERE’ clause.

Below are examples of ways to limit the number of results in your SQL query:

IBM DB2 Syntax

SELECT * FROM PRODDTA.F0911
FETCH FIRST 10 ROW ONLY;

SQL Server Syntax

SELECT TOP 10
FROM PRODDTA.F0911;

Oracle Syntax

SELECT * FROM PRODDTA.F0911
WHERE ROWNUM <= 10;

Source: IBM Knowledge Center | W3Schools.com

If you have any questions, comments or ideas, please leave them in the comments.

Author

Stewart Schatz

Career: Principal CNC Consultant for Syntax Systems Limited specializing Oracle JD Edwards EnterpriseOne and the technology that supports it. Side Hustle: Owner/Operator of E1Tips.com Location: Lancaster, PA USA  What I like to do: Invest in Family, Explore Technology, Lead Teams, Share Knowledge/Experience, Hunt, Hike, etc.

stewart@9rains.com

http://stewartschatz.com