使用DENSE_RANK解析函数。因此，如果您试图为每个id_assunto查找按data然后data_inclusao然后id_andamento排序的第二大行，则可以使用用途：

SELECT *
FROM   (
  SELECT j.*,
         DENSE_RANK() OVER (
           PARTITION BY id_assunto
           ORDER BY data DESC, data_inclusao DESC, id_andamento DESC
         ) AS rnk
  FROM   ldesk.jur_andamento j
)
WHERE rnk = 2;

如果你想要最大的data，然后是data_inclusao，然后是第二高的id_andamento，你可以分两步过滤：

SELECT *
FROM   (
  SELECT t.*,
         DENSE_RANK() OVER (
           PARTITION BY id_assunto
           ORDER BY id_andamento DESC
         ) AS id_rnk
  FROM   (
    SELECT j.*,
           DENSE_RANK() OVER (
             PARTITION BY id_assunto
             ORDER BY data DESC, data_inclusao DESC
           ) AS data_rnk
    FROM   ldesk.jur_andamento j
  ) t
  WHERE  data_rnk = 1
)
WHERE  id_rnk = 2;

而不是最大xou可以使用窗口函数ROW_NUMBER获得第二行，或任何其他的问题

select sja.descricao
          from ldesk.jur_andamento sja
         where sja.id_andamento =
  (SELECT id_andamento FROM
               (select id_andamento, ROW_NUMBER() OVER(PARTITION BY id_assunto ORDER BY id_andamento DESC) rn
                from ldesk.jur_andamento sja2
                 where sja2.id_assunto = a1.id_assunto
                   and data_inclusao in
                       (select max(data_inclusao)
                          from ldesk.jur_andamento sja2
                         where sja2.id_assunto = a1.id_assunto
                           and data in
                               (select max(data)
                                  from ldesk.jur_andamento
                                 where id_assunto = a1.id_assunto)
                      )
                 
                ) andamento
  WHERE rn = 2)

ORA-00942: table or view does not exist

fiddle

首先，如前所述，您应该适当地重写代码以使用ORDER BY子句。倒数第二个最大值相当于第二个最小值。
然后，执行这种类型任务的适当函数是row_number（）。

(SELECT descricao
   FROM (SELECT sja.descricao,
                row_number() over(ORDER BY sja.data, sja.data_inclusao, sja.id_andamento) rn
           FROM ldesk.jur_andamento sja)
  WHERE rn = 2) ult_andamento_descr

但是，也可以使用OFFSET：

(SELECT descricao FROM ldesk.jur_andamento sja
  ORDER BY sja.data, sja.data_inclusao, sja.id_andamento
  OFFSET 1 rows FETCH FIRST 1 rows ONLY) ult_andamento_descr