SQL help

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 Received on Sat Oct 12 1996 - 00:00:00 CEST