Re: Returning DISTINCT rows when SQL *DOES NOT* contain distinct!

From: imc <icohen33_at_gmail.com>
Date: 20 May 2005 06:22:43 -0700
Message-ID: <1116595363.401398.41680@g49g2000cwa.googlegroups.com>

Hi Carlos,

I do the same code as you and get different results.

SQL> create table prueba (id number, txt varchar2(10));

Table created.

SQL>
SQL> insert into prueba values (1,'ONE');

1 row created.

SQL>
SQL>
SQL> insert into  prueba values (1,'ONE');

1 row created.

SQL>
SQL> insert into prueba values (1,'ONE');

1 row created.

SQL>
SQL> insert into prueba values (2,'TWO');

1 row created.

SQL>
SQL> insert into prueba values (1,'TWO');

1 row created.

SQL> select * from prueba;

        ID TXT
---------- ----------

         1 ONE
         1 ONE
         1 ONE
         2 TWO
         1 TWO

SQL> select id,
  2         txt

  3 from ( select id, txt, rank() over (partition by id, txt order by rowid) v_rank from prueba)
  4 where v_rank = 1;

        ID TXT
---------- ----------

         1 ONE
         1 TWO
         2 TWO

ICohen Received on Fri May 20 2005 - 08:22:43 CDT