Search code examples
oracle-databasedistinctrownum

Combine Rownum with Distinct


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.


Solution

  • 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;