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.