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

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Thu, 12 May 2005 17:04:16 -0700
Message-ID: <1115942401.578168@yasure>

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;
>
> ID TXT
> ---------- ----------
> 1 ONE
> 2 TWO
>
> SQL>
>
> No DISTINCT, no GROUP BY.
>
> Cheers.
>
> Carlos.

You make me just want to suggest:

SELECT UNIQUE id
FROM ....; Also doesn't contain the keyword word DISTINCT.

Why hasn't anyone asked the OP what the point of the exercise is? I fail to see a premise beyond "my instructor gave me homework."

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Thu May 12 2005 - 19:04:16 CDT