Re: SQL help

Les Gainous wrote:
>
> 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
> > -----------------------------------------------------------------

-- 
Les Gainous,

I don't think the problem at hand is quite as simple as you make it to
be.

Here's another look at Eric's data...

 Example:
 member_id      dept_id
 1            AAAA
 1            BBBB
 1            CCCC
 2            AAAA
 3            DDDD
 3            CCCC

Here's whar Eric needs.....


> > >

> > > So I want to select the FIRST OCCURENCE 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


Your solution using min(dept_id) will return the lowest dept_id within a
given member_id...
This however, need not be the first occurence of member_id and dept_id
as is evident from Eric's choice
of data.
Your query would have returned....
	member_id	dept_id
	   1		  AAAA
	   2 		  AAAA
	   3		  CCCC  .... but Eric needs "DDDD" here since that is the first
occurence of dept_id
within the given member_id. 

Thanks,

-----------------------------------------------------------------
Naresh Ramamurti 			nramamur_at_worldnet.att.net
-----------------------------------------------------------------