Re: Selct DISTINCT

trpost_at_gmail.com wrote:
> I am looking to query the first occurance of a row based on a distinct
> column. Here is an example result set:
>

Define "first occurance" - data from an RDBMS gets presented unordered, and there is no guarantee that you will get the same ordered set next time, unless you specifically order it!

> SELECT * FROM TABLE
>
> ID Name SSN
> 456 Todd 1235
> 955 Jane 2345
> 955 Jane
> 988 Jack 3547

No order by clause ...
>
>
> If I do this I get the expected number of results:
> SELECT DISTINCT(ID) FROM TABLE;
>
> ID
> 456
> 955
> 988
>
> However if I do this I get all rows despite the distinct keyword:

Not if you had another row with (955, Jane), you don't:

SQL> select * from blah;

        ID NAME SSN
---------- ---------- ----------

       456 Todd             1235
       966 Jane             2345
       966 Jane
       988 Jack             3547
       966 Jane

SQL> SELECT DISTINCT(ID), Name, SSN FROM blah;
        ID NAME              SSN
---------- ---------- ----------
       456 Todd             1235
       966 Jane
       988 Jack             3547
       966 Jane             2345

> SELECT DISTINCT(ID), Name, SSN FROM TABLE;
>
> ID Name SSN
> 456 Todd 1235
> 955 Jane 2345
> 955 Jane
> 988 Jack 3547
>
> So it looks like distinct works on the row, looking for an entirely
> distinct row. What I want to do is look only at one column and return
> the first row occurance.
>
> How can this be done? I am using an Oracle 10G database.
>
> Thanks!

inline view, subquery, analytic functions pop to mind. The exact syntax is left for the OP, depending on how many columns there are to be inspacted.

-- 

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up