Distinct grouping [message #303281] |
Thu, 28 February 2008 12:02  |
jackstraw22
Messages: 1 Registered: February 2008
|
Junior Member |
|
|
I have a query:
select mi.memberid, mi.name, pt.programtype
from mms.member_info mi1,
mms.entity en1,
mms.enrollment_gt eg1,
mms.programtypes_gt pt1,
mms.programs_gt pg1,
mms.statuscodes sc1
where mi1.entityid = en1.id
and mi1.entityid = eg1.entityid
and mi1.memberid = eg1.memberid
and eg1.program_id = pg1.program_id
and eg1.enrollmentstatus = sc1.id
and mi1.state = pg1.state
and pt1.type_id = pg1.type_id
The results of this query bring back data such as this:
memberid name programtype
1 John Smith VL
1 John Smith WC
1 John Smith PP
3 Sue Smith PP
3 Sue Smith VL
Is it possible to group the result set so that John Smith with a memberid of 1 is shown only one time, but all three of his program types are shown?
|
|
|
|
Re: Distinct grouping [message #303284 is a reply to message #303281] |
Thu, 28 February 2008 12:06   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Hi,
If you producing a report search for break statement in sql reference manual. If not you have try looking into analytic functions and case statement.
Please take some time to read the forum guidelines.
Regards
Raj
|
|
|
Re: Distinct grouping [message #303290 is a reply to message #303281] |
Thu, 28 February 2008 12:46  |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
You do realize that this is not the query you ran as the aliases for the selected columns do match the aliases for the tables.
|
|
|