CODEWITHSUNDEEP
oracle-databasedistinctrownum
Pedro Plaza Garcia
Pedro Plaza Garcia

Reputation: 5

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.

Upvotes: 0

Views: 786

Answers (3)

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

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;

Upvotes: 1

Sanders the Softwarer
Sanders the Softwarer

Reputation: 2496

It's possible with rownum, but needs some accuracy:

select run_date
from 
  ( select run_date, rownum r# 
    from 
      ( select run_date 
        from transactions 
        where primary_customer_id ='cliente' 
        group by run_date 
        order by run_date desc
      )
  )
where r# = 2;

Too bad that while it really works, nobody can guarantee that it would be forever. Result of this query is theoretically unpredictable and tricks which uses it can fail at new versions of the RDBMS kernel as it was with Oracle 10g and GROUP BY sorting. So it's better to use something like

select min(run_date)
from 
  ( select lead(run_date) over (order by run_date) run_date
    from 
      ( select distinct run_date 
        from transactions 
        where primary_customer_id ='cliente'
      )
  )
where r# = 2;

P.S. Anyway, combining DISTINCT with GROUP BY as you did is stutter.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

You should not use alias in where so use rownum and not the alias r_

Select run_date, rownum r_ from(
      select distinct run_date 
      from transactions 
      where primary_customer_id ='cliente' 
      order by run_date desc)) t
where rownum = 2;

and using distinct you don't need group by.

Group by is for aggregation function as min() max() count() ...

and you could try directly without subquery 

      select distinct run_date 
      from transactions 
      where primary_customer_id ='cliente' 
      and rownum =2
      order by run_date desc

Upvotes: 0

Related Questions