Home » SQL & PL/SQL » SQL & PL/SQL » Retrieving Data Based on MAX()
Retrieving Data Based on MAX() [message #346825] Tue, 09 September 2008 16:02 Go to next message
sisterwolf
Messages: 2
Registered: September 2008
Location: VA
Junior Member
I want to retrieve specific data based on MAX(Action_Date). However, I'm stumped as to how to write it. Basically, I want the last dated contact_trk for each prospect_id and camp_trk.

The below works fine for only prospect_id.
select prospect_id, max(action_date)
from promo_hist

ex.
123abc.....5/7/08
456def.....2/1/08

I tried the below code but got multiple rows.
select prospect_id, camp_trk, contact_trk, max(action_date)
from promo_hist

ex.
123abc...rrn_200801...rrn_final........5/7/08
123abc...rrn_200801...rrn_wave_4..12/9/07
123abc...rrn_200801...rrn_wave_3..11/16/07

What I want is the below result.
ex.
123abc...rrn_200801...rrn_final...5/7/8

Could somebody please help? Thanks.
Re: Retrieving Data Based on MAX() [message #346827 is a reply to message #346825] Tue, 09 September 2008 16:13 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

I am sorry to see that both GOOGLE & Search Function on this forum are broken for you.
Repairs have been initiated.
More information will be posted after repairs have been completed.
Please wait patiently for additional feedback.
Re: Retrieving Data Based on MAX() [message #346830 is a reply to message #346825] Tue, 09 September 2008 16:20 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
When this gives you the IDs and Dates you want

select prospect_id, max(action_date)
from promo_hist


Then just put in in a sub-select into the where-clause

SELECT *
FROM   Promo_Hist
WHERE  (Prospect_Id,
        Action_Date) IN (SELECT Prospect_Id,
                                MAX(Action_Date)
                         FROM   Promo_Hist)



Code is untested, since you didn't provide a test case. I strongly suspect there is at least one additional "group by" needed.

Also, read the Forum guidelines on how to format posts properly. There might be better solutions, but they would depend on your exact database version.

[Updated on: Tue, 09 September 2008 16:21]

Report message to a moderator

Re: Retrieving Data Based on MAX() [message #346831 is a reply to message #346825] Tue, 09 September 2008 16:23 Go to previous messageGo to next message
developer69
Messages: 6
Registered: September 2008
Junior Member
You can try this:

select prospect_id, camp_trk, max(action_date)
from promo_hist
group by prospect_id, camp_trk


Thanks

[Updated on: Tue, 09 September 2008 16:26]

Report message to a moderator

Re: Retrieving Data Based on MAX() [message #347040 is a reply to message #346825] Wed, 10 September 2008 08:39 Go to previous messageGo to next message
sisterwolf
Messages: 2
Registered: September 2008
Location: VA
Junior Member
Thanks ThomasG. Your code worked with the addition of "group by".

The below code is what I needed.

select prospect_id, camp_trk_code, contact_trk_code, action_date
from promo_hist
where (prospect_id, action_date) in
(select prospect_id, max(action_date)
from promo_hist
group by prospect_id)
Re: Retrieving Data Based on MAX() [message #347042 is a reply to message #347040] Wed, 10 September 2008 08:44 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Great. Thanks for the feedback. Thumbs Up
Re: Retrieving Data Based on MAX() [message #347118 is a reply to message #346825] Wed, 10 September 2008 15:52 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Are you sure that it is correct, wouldn't it be the following f you want the last ticket on prospect_id AND camp_trk_code

select prospect_id, camp_trk_code, contact_trk_code, action_date
from promo_hist
where (prospect_id, camp_trk_code, action_date) in
(select prospect_id, camp_trk_code, max(action_date)
from promo_hist
group by prospect_id,camp_trk_code)
Re: Retrieving Data Based on MAX() [message #347131 is a reply to message #347118] Wed, 10 September 2008 17:45 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
sisterwolf, what is the exact SQL and expected output as what you provided is not valid syntax.

Quote:


The below works fine for only prospect_id.
select prospect_id, max(action_date)



e.g. -
CREATE TABLE PROMO_HIST (PROSPECT_ID  VARCHAR2(8),
	  		 ACTION_DATE  DATE,
			 CAMP_TRK     VARCHAR2(20),
			 CONTRACT_TRK VARCHAR2(20));

						 
INSERT INTO PROMO_HIST VALUES ('123abc', to_date('05/07/08','MM/DD/YY'),'rrn_200801','rrn_final');
INSERT INTO PROMO_HIST VALUES ('123abc', to_date('12/09/07','MM/DD/YY'),'rrn_200801','rrn_wave3');
INSERT INTO PROMO_HIST VALUES ('456def', to_date('02/01/08','MM/DD/YY'),'rrn_200801','rrn_wave2');
  
commit;
 
 
 
select prospect_id, max(action_date)
from promo_hist;


output:

Table created.
1 row created.
1 row created.
1 row created.
Commit complete.
ORA-00937: not a single-group group function



The sub-query approach listed is correct for finding the max date rec selection by prospect_id and camp_trk_code.

But if you truly want the max date record by prospect_id
alone you can try out the analytical function method

select prospect_id,camp_trk,contract_trk,action_date from  (
select prospect_id,  
       camp_trk, 
	   contract_trk, 
	  action_date,
	  row_number()   
 over (partition by prospect_id order by action_date desc) r
   
from promo_hist) 
Where r < 2;

PROSPECT CAMP_TRK             CONTRACT_TRK         ACTION_DA
-------- -------------------- -------------------- ---------
123abc   rrn_200801           rrn_final            07-MAY-08
456def   rrn_200801           rrn_wave2            01-FEB-08

2 rows selected.



This will insure one rec by prospect id (latest rec by action date irrespoective of the other field vals

Best Regards
Harry
Re: Retrieving Data Based on MAX() [message #347503 is a reply to message #347131] Thu, 11 September 2008 22:24 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
And another:
select prospect_id
,      camp_trk_code
,      MAX(contact_trk_code) KEEP (DENSE_RANK LAST ODER BY action_date)
,      MAX(action_date)
from promo_hist
group by prospect_id,camp_trk_code


Ross Leishman
Previous Topic: Reverse Word
Next Topic: Help with query rewrite using analytic function
Goto Forum:
  


Current Time: Wed Dec 07 06:37:50 CST 2016

Total time taken to generate the page: 0.16218 seconds