Highest / Lowest pl/sql

Question

I have made a procedure to display the highest and the lowest popular Items for a particular time of a given date. The procedure works with no errors or exceptions and every thing is functioned. As you can see, the queries are repeated twice for the purpose of showing the first record on Items, but the only differences are in Order (ASC and DESC)! Is there any way I can reduce the amount of the code? How can I show the highest and lowest Items in one query rather than two? I only want to make the code neat and easy to read. Any help would be much appreciated.

CREATE OR REPLACE PROCEDURE highest_lowest(param IN DATE)
AS
  v_pno_low  NUMBER(5);
  v_pno_high NUMBER(5);
  BEGIN

    SELECT item_no INTO v_pno_low
    FROM (
      SELECT
        items.item_no,
        sum(items.quantity)                 AS total,
        to_char(prodcution_d, 'dd-mm-yyyy') AS pro_date
      FROM items
        JOIN parts ON parts.serial_no = items.serial_no
      GROUP BY item_no, to_char(prodcution_d, 'dd-mm-yyyy')
      ORDER BY sum(items.quantity) ASC)
    WHERE rownum = 1
          AND pro_date = to_char(param_date, 'mm-yyyy');

    dbms_output.put_line('LOWEST ITEM: ' || v_pno_low);

    SELECT item_no INTO v_pno_low
    FROM (
      SELECT
        items.item_no,
        sum(items.quantity)                 AS total,
        TO_CHAR(prodcution_d, 'dd-mm-yyyy') AS pro_date
      FROM items
        JOIN parts ON parts.serial_no = items.serial_no
      GROUP BY item_no, to_char(prodcution_d, 'dd-mm-yyyy')
      ORDER BY SUM(items.quantity) DESC)
    WHERE rownum = 1
          AND pro_date = to_char(param_date, 'mm-yyyy');

    dbms_output.put_line('HIGHEST POPULAR ITEM: ' || v_pno_high);

  END;
  /

Answer 1

Use the FIRST and LAST functions to get both the maximum and the minimum in one query:

--Step 2: The SERIAL_NO for the smallest and largest quantity per day.
select
    min(serial_no) keep (dense_rank first order by quantity_sum) low_serial_no,
    min(serial_no) keep (dense_rank last  order by quantity_sum) high_serial_no
into v_pno_low, v_pno_high
from
(
    --Step 1: Sum of quantity, per day, for the specified month.
    select
        items.serial_no,
        trunc(production_date, 'day') the_date,
        sum(quantity) quantity_sum
    from items
    join parts
        on items.serial_no = parts.serial_no
    where trunc(production_date, 'month') = trunc(param_date, 'month')
    group by items.serial_no, trunc(production_date, 'day')
);

The KEEP syntax is a bit confusing at first. The outer query gets the minimum values of SERIAL_NO, but only among rows for the first (lowest) QUANTITY_SUM and then the last (highest) QUANTITY_SUM.

The query also uses TRUNC instead of TO_CHAR. It's usually best to keep dates as dates and avoid any conversion functions.