SQL & PL/SQL

Hi All,

Existing sql query...using rn=1

SELECT a.*

FROM   (SELECT Min(check_num) over( PARTITION BY invoice_num) check_num,

               invoice_num,

               Min(check_date)over( PARTITION BY invoice_num) PAYMENT_DATE,

               Row_number()   over (PARTITION BY invoice_num ORDER BY check_date)      rn

        FROM   table_x) a

WHERE rn = 1

If i see the data has 2 different rn values...only one row is coming because of rn=1 

WITH table_x

     AS (SELECT '1111'                            check_num,

                '1111-1'                          invoice_num,

                To_date('2/7/2012', 'MM/DD/YYYY') check_date

         FROM   dual

         UNION ALL

         SELECT '2222'                            check_num,

                '1111-1'                          invoice_num,

                To_date('2/7/2012', 'MM/DD/YYYY') check_date

         FROM   dual)

SELECT Min(check_num) over( PARTITION BY invoice_num) check_num,

       invoice_num,

       Min(check_date)over( PARTITION BY invoice_num) PAYMENT_DATE,

       Row_number() over ( PARTITION BY invoice_num ORDER BY check_date) rn

FROM   table_x

do you want me to use RANK() function?

Please suggest if any impact on this?

Oracle Version : 11.2

Thanks

Message was edited by: Rajesh123 Updated output....