CREATE CURSOR tbl_num_np_order (id_order INT, base_num INT)
CREATE CURSOR tbl_num_np_issue (base_num INT, num_newspaper INT)

INSERT INTO tbl_num_np_order VALUES (1, 996)
INSERT INTO tbl_num_np_order VALUES (1, 997)
INSERT INTO tbl_num_np_order VALUES (1, 998)
INSERT INTO tbl_num_np_order VALUES (1, 999)
INSERT INTO tbl_num_np_order VALUES (2, 996)
INSERT INTO tbl_num_np_order VALUES (2, 997)
INSERT INTO tbl_num_np_order VALUES (2, 998)
INSERT INTO tbl_num_np_order VALUES (2, 999)
INSERT INTO tbl_num_np_order VALUES (2, 1000)


INSERT INTO tbl_num_np_issue VALUES ( 996, 49)
INSERT INTO tbl_num_np_issue VALUES ( 997, 50)
INSERT INTO tbl_num_np_issue VALUES ( 998, 1)
INSERT INTO tbl_num_np_issue VALUES ( 999, 2)
INSERT INTO tbl_num_np_issue VALUES (1000, 3)

SELECT t3.id_order, tbni.num_newspaper, t3.max_n FROM ;
  (SELECT t1.id_order, tbno.base_num, tbni.num_newspaper as max_n ;
  FROM ;
      (SELECT id_order, MAX(base_num) max_n ;
       FROM tbl_num_np_order ;
       GROUP BY id_order) t1 ; 
      inner JOIN tbl_num_np_issue tbni on t1.max_n = tbni.base_num ;
      inner join tbl_num_np_order tbno on t1.id_order = tbno.id_order) t3 ;
  inner join tbl_num_np_issue tbni on t3.base_num = tbni.base_num

SELECT tbno.id_order, ;
  tbni.num_newspaper, ;
  tbni2.num_newspaper ;
 FROM tbl_num_np_order tbno ;
  INNER JOIN tbl_num_np_issue tbni ;
   ON tbni.base_num = tbno.base_num ;
 INNER JOIN (SELECT id_order, ;
               MAX(base_num) max_n ;
              FROM tbl_num_np_order ;
              GROUP BY id_order) t1 ;
  ON t1.id_order = tbno.id_order ;
 INNER JOIN tbl_num_np_issue tbni2 ;
  ON tbni2.base_num = t1.max_n

SELECT tbno.id_order,
         tbni.num_newspaper,
         MAX (tbni.num_newspaper)
            KEEP (DENSE_RANK LAST ORDER BY tbno.base_num)
            OVER (PARTITION BY tbno.id_order)
            max_num_newspaper
    FROM    tbl_num_np_order tbno
         INNER JOIN
            tbl_num_np_issue tbni
         ON tbni.base_num = tbno.base_num

select tbno.id_order, tbni1.num_newspaper,  tbni2.num_newspaper
from tbl_num_np_order tbno
    inner join (select id_order, max(base_num) max_n from tbl_num_np_order group by id_order) t1 on tbno.id_order = t1.id_order
    inner join tbl_num_np_issue tbni1 on tbno.base_num = tbni1.base_num
    inner join tbl_num_np_issue tbni2 on t1.max_n = tbni2.base_num

CREATE TABLE TBL_NUM_NP_ISSUE (         - таблица, содержащая данные о выходах изданий
       ID_NEWSPAPER Smallint NOT NULL,  - идентификатор издания
       NUM_NEWSPAPER Smallint NOT NULL, - годовой номер издания
       DATE_ISSUE Date NOT NULL,        - дата выхода номера
       BASE_NUM Smallint NOT NULL       - сквозной номер издания

CREATE TABLE TBL_NUM_NP_ORDER (         - таблица, содержащая данные о выходах заказов
       ID_NEWSPAPER Smallint NOT NULL,  - идентификатор издания, в котором выходит заказ
       ID_ORDER Integer NOT NULL,       - идентификатор заказа
       NUM_NEWSPAPER Smallint NOT NULL, - вот этого поля здесь не должно быть, я как раз занимаюсь тем, что убираю
             его отсюда, (в прошлой версии программы num_newspaper как раз и был сквозным номером).
       D Smallint DEFAULT 0 NOT NULL,   - удалён ли выход, если 0 - выход будет, если 1 - значит выход убрали
       BASE_NUM Smallint NOT NULL       - сквозной номер