Home » SQL & PL/SQL » SQL & PL/SQL » Distinct grouping (Oracle 10G)
Distinct grouping [message #303281] Thu, 28 February 2008 12:02 Go to next message
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 #303283 is a reply to message #303281] Thu, 28 February 2008 12:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just select the first part of the name, John Smith, and use distinct.

Regards
Michel
Re: Distinct grouping [message #303284 is a reply to message #303281] Thu, 28 February 2008 12:06 Go to previous messageGo to next message
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 Go to previous message
joy_division
Messages: 4640
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.
Previous Topic: Update/insert logic results in duplicate rows.
Next Topic: Spliting string to column!
Goto Forum:
  


Current Time: Sun Dec 04 19:02:56 CST 2016

Total time taken to generate the page: 0.07455 seconds