Re: SQL help

Why not ?

select member_id, MIN(dept_id) dept_id
  from member
  group by member_id
;

-- 
G'luck !
-----------------------------------------
Garry M. FIlimonov
LASU TRINITI, Troitsk, MR, Russia
garry_at_triniti.troitsk.ru
7-(095)-334-0408

Eric Weiss <eweiss_at_llnl.gov> wrote in article <32600FA8.41C6_at_llnl.gov>...


> I have a table (member) that contains two columns, member_id and


> dept_id.  Each member id can belong to multiple depts.  I have been

> trying to write a query that grabs the first occurance of the member id

> as a way of "defaulting" a member to a dept.

> Example:

>   member_id      dept_id

>       1            AAAA

>       1            BBBB

>       1            CCCC

>       2            AAAA

>       3            DDDD

>       3            CCCC

> 

> So I want to select the first occurance of member_id and dept id from

> the member table so my results look like:

> 

>   member_id      dept_id

>      1             AAAA

>      2             AAAA

>      3             DDDD

> 

> I have tried the following query:

> 

> select member_id, dept_id

>   from member

>  where member_id in (

>          select member_id 

>            from member

>           where ROWNUM < 2 )

> 

> This query only returns the first member_id:

> 

>   member_id      dept_id

>      1             AAAA

> 

> This is what I want, but for ALL member id's, not just the first one

> found.

> Does anyone have a suggestion on how to make this query work, or a

> different approach I haven't considered.  I am sure there is an obvious

> answer but it is eluding me.

> 

> Thanks for any help you can give,

> Eric Weiss

> eweiss_at_llnl.gov

>