Re: Selct DISTINCT

Comments embedded.
On Jan 29, 12:33 pm, trp..._at_gmail.com wrote:
> I am looking to query the first occurance of a row based on a distinct
> column.

Hmmm ...

> Here is an example result set:
>
> SELECT * FROM TABLE
>
> ID      Name   SSN
> 456    Todd    1235
> 955    Jane     2345
> 955    Jane
> 988    Jack     3547
>
> If I do this I get the expected number of results:
> SELECT DISTINCT(ID) FROM TABLE;
>
> ID
> 456
> 955
> 988
>

Yes, your set consists of one column of data.

> However if I do this I get all rows despite the distinct keyword:
> SELECT DISTINCT(ID), Name, SSN FROM TABLE;
>
> ID      Name   SSN
> 456    Todd    1235
> 955    Jane     2345
> 955    Jane
> 988    Jack     3547
>

This is no longer a one-column set, yet it produces the correct result.

> So it looks like distinct works on the row, looking for an entirely
> distinct row.

Correct.

> What I want to do is look only at one column and return
> the first row occurance.

If you choose the correct column such a feat is possible.

>
> How can this be done? I am using an Oracle 10G database.

You'll need to use a column you didn't define, have no control over, yet is present in every table that exists in Oracle: ROWID. You may need to play with this a bit, but an example would be:

SQL> select id, name, ssn
  2 from mytable
  4 where rowid in (select min(rowid) from mytable group by id)   3 /

        ID NAME                        SSN
---------- -------------------- ----------
       955 Jane                       2345
       456 Todd                       1235
       988 Jack                       3547

SQL> Your mileage may vary.

>
> Thanks!

David Fitzjarrell Received on Tue Jan 29 2008 - 13:20:02 CST