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

>>Nothing in a WHERE clause will help you; only DISTINCT or GROUP BY
will return unique records.

That's not true:

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

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

1 fila creada.

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

1 fila creada.

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

1 fila creada.

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

1 fila creada.

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

1 fila creada.

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

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

         1 ONE
         2 TWO

SQL> No DISTINCT, no GROUP BY.

Cheers.

Carlos. Received on Thu May 12 2005 - 01:57:30 CDT