Re: SQL help

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
-----------------------------------------------------------------