SQL & PL/SQL

I am running a query against what would basically be considered an Orders db (Oracle 10g). I have 4 tables that I am querrying against.
This is an abbreviated version of the result set.

CUS_ID	ORD_NUM	ACT_IND	                                     PRD_ID	                              ORD_COMP_DT
794102	496342	N - New     	1752AOC03GLVVNYXA5MDNRWCNYXA0MW    4/4/2006
794102	709153	C - Change              1752AOC03GLVVNYXA5MDNRWCNYXA0MW	 4/10/2008
794102	741641	D - Discontinue	1752AOC03GLVVNYXA5MDNRWCNYXA0MW	 7/15/2008
794102	769121	D - Discontinue	1752AOC03GLVVNYXA5MDNRWCNYXA0MW	 3/3/2009

I have attempted to use the MAX(ORD_COMP_DT) as well as MAX(ORD_NUM) with no success. I still get the same 4 rows returned.
What I am trying to accomplish is a result that will show me only the latest pertinant information for the CUS_ID (which is unique for each PRD_ID).

SELECT max(ORD_COMP_DT) as COMP_DT,
max(ORD_NUM) as ORD
from table

So my result set would be as:

CUS_ID	   ORD_NUM	ACT_IND	                                     PRD_ID	                                   ORD_COMP_DT
794102	    769121	            D - Discontinue	1752AOC03GLVVNYXA5MDNRWCNYXA0MW	    3/3/2009

With my limited knowledge, I am guessing that this should be a subquery within my larger query against the multiple tables. I have looked through the forum and tried to use some examples but still no success.

Updated* - I have managed to get the result set close to what I am looking for by simply using the MAX function on both ORD_NUM and ORD_COMP_DT and Group BY;however becuase there are multiple instances in the ACT_IND column my results go beyond 1 line when I include that field. So at this point, can anyone suggest how I can handle that field yet still only return the latest ORD_NUM and ORD_COMP_DT ?


Not to confuse the issue, but ultimately I would like to be able to actually capture both the New and Discontinue fields (PIVOT - I understand is not available on Oracle 10g, so meaning an alternative method) and pivot those like this:

CUS_ID	    NEW_ORD_NUM	DISC_ORD_NUM	PRD_ID	                                                   NEW_ORD_COMP_DT	DISC_ORD_COMP_DT
794102	    496342	                769121	           1752AOC03GLVVNYXA5MDNRWCNYXA0MW	   4/4/2006	                3/3/2009

I am sorry for the rambling post - I have been trying to solve my own problem before actually hitting the the Post button, but I am stuck.

Thanks for looking.

G

Edited by: GMoney on Jul 12, 2012 11:24 AM

Edited by: GMoney on Jul 12, 2012 11:27 AM

Edited by: GMoney on Jul 12, 2012 11:29 AM