Shown below are examples in which a count of the total number of products whose price (PRICE) is $50.00 or more is obtained from the stock table (STOCK), along with the product names of these products (PNAME), and then the resulting list of products is sorted by quantity (SQUANTITY).
- When the facility for returning the total number of hits is not used
SELECT COUNT(*) FROM STOCK WHERE PRICE>=5000
SELECT PNAME FROM STOCK WHERE PRICE>=5000 ORDER BY SQUANTITY
- Explanation
- When the facility for returning the total number of hits is not used, two SQL statements are required.
- When the facility for returning the total number of hits is used
SELECT COUNT(*) OVER(), PNAME
FROM STOCK WHERE PRICE>=5000 ORDER BY SQUANTITY
- Explanation
- Because the underlined sections of the two SQL statements are the same, you can use the facility for returning the total number of hits in order to combine the two SQL statements into a single SQL statement, and so obtain the total number of hits in the first fetch operation.