There are two ways to limit the result.
Suppose that you write an application that requires information on only the 10 student with the highest total marks. To return only the rows of the Students table for those 20 student, This can be achived by Fetch First or Limit clause.
Also, This clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.
The LIMIT clause allows you to limit the number of rows returned by the query.
SELECT select_list FROM table_name LIMIT n [OFFSET m]; |
Where,
'm' is the number of rows to skip before returning the n rows.
'n' is the number of rows to be returned.
You can also write the LIMIT syntax like below,
LIMIT m, n; |
This syntax represents skipping m rows and returning the next n rows from the result set.
Below is a selection from the "Product" table in the DB2 database.
ProductID | ProductDesc | Category | SRP | QtyOnHand | TotalValue |
---|---|---|---|---|---|
7001 | Mouse | Accessories | 75.00 | ||
7002 | Harddrive | 65.00 | 20 | 1,300 | |
7003 | Keyboard | Accessories | 36.00 | 33 | 1,118.00 |
7004 | RAM | Components | 23.50 | 16 | 376.00 |
7005 | Honda | Bikes | 1,200 | ||
7006 | PEN | 7.45 | 10 | 74.50 | |
7007 | Cddrive | Accessories | 75.00 | ||
7008 | Speaker | Accessories | 75.00 |
Using the LIMIT syntax to fetch the first 4 rows from a "Product" table.
SELECT * FROM Product LIMIT 4; |
ProductID | ProductDesc | Category | SRP | QtyOnHand | TotalValue |
---|---|---|---|---|---|
7001 | Mouse | Accessories | 75.00 | ||
7002 | Harddrive | 65.00 | 20 | 1,300 | |
7003 | Keyboard | Accessories | 36.00 | 33 | 1,118.00 |
7004 | RAM | Components | 23.50 | 16 | 376.00 |
To skip the first 2 products and return the next 3 products, We use the LIMIT OFFSET clause as follows:
SELECT * FROM Product LIMIT 3 OFFSET 2; |
ProductID | ProductDesc | Category | SRP | QtyOnHand | TotalValue |
---|---|---|---|---|---|
7003 | Keyboard | Accessories | 36.00 | 33 | 1,118.00 |
7004 | RAM | Components | 23.50 | 16 | 376.00 |
7005 | Honda | Bikes | 1,200 |
If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!