Re: SQL help

From: Les Gainous <lesgainous_at_earthlink.net>
Date: 1996/10/13
Message-ID: <3261C510.14A9_at_earthlink.net>#1/1


Eric (and Naresh),

Try this:

select		member_id,
		min(dept_id)
from		member
group by	member_id


good.luck = true

-- 
 Les Gainous, lesgainous_at_earthlink.net

 Visit my web page at
      http://home.earthlink.net/~lesgainous

 Looking for a Client-Server job in California?
      http://home.earthlink.net/~lesgainous/jobs.html


Naresh Ramamurti wrote:

>
> Eric Weiss wrote:
> >
> > 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
>
> --
>
> Eric,
>
> Try this...
>
> select
> member_id, dept_id
> from
> member a
> where
> rowid =
> (
> select
> min(rowid)
> from
> member b
> where
> a.member_id = b.member_id
> )
>
> ;
>
> This should provide you with the foll. output:-
>
> MEMBER_ID DEPT
> --------- ----
> 1 AAAA
> 2 AAAA
> 3 DDDD
>
> -----------------------------------------------------------------
> Naresh Ramamurti nramamur_at_worldnet.att.net
> -----------------------------------------------------------------
Received on Sun Oct 13 1996 - 00:00:00 CEST

Original text of this message