I Have this query in Oracle:
Select run_date
from (select distinct run_date
from transactions where primary_customer_id ='cliente'
group by run_date
order by run_date desc);
I Need to show the second row, but it not possible with a row num.
Select run_date, rownum r_ from(select distinct run_date
from transactions
where primary_customer_id ='cliente'
group by run_date
order by run_date desc))
where r_ = 2;
Can i help me?
thank a lot.
You need NOT use both GROUP BY
and DISTINCT
. Also note that simple ROWNUM = 2
will never be satisfied in Oracle.
This works in 10g and 11g.
WITH r
AS ( SELECT DISTINCT run_date
FROM transactions
WHERE primary_customer_id = 'cliente'
ORDER BY run_date DESC)
SELECT run_date
FROM (SELECT run_date, ROWNUM rn FROM r)
WHERE rn = 2;
In Oracle 12c, you may achieve the same result with query like this.
SELECT run_date
FROM r -- without ORDER BY inside cte, r
ORDER BY run_date DESC
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;