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

From: Scott Mattes <Scott_at_TheMattesFamily.ws>
Date: Thu, 12 May 2005 09:09:25 -0400
Message-ID: <Q9GdneREt_SjyB7fRVn-qw@adelphia.com>

Carlos wrote:

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

But he doesn't have control over the 'from'. He might be able to do something similar by moving the 'select' in the 'from' to a 'where' something like

where rowid = ( select * from ( select id, txt, rowid, 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 - 08:09:25 CDT