Maximum Date in a group of records

I have a table that I need to find the maximum date per Doc_No per Parent_Doccategory. I can get the maximum date per Doc_No but I can't get the information for the next level. The following script is getting the maximum date per airplane but I should get a maximum date each time the parent_doccategory changes. Can anyone help? This is the script so far:

CREATE PROCEDURE [dbo].[sp_Maximum_Change_Date] AS

select Effective_Updated , parent_doc_no, parent_doccategory from gdb_01_4.dbo.aircraft_changes As S
where effective_updated = (select max(effective_updated)
from gdb_01_4_test.dbo.aircraft_changes where
parent_doc_no = S.parent_doc_no) group by parent_doc_no,parent_doccategory, effective_updated, order by parent_doc_no
GO

PARENT_DOC_NOPARENT_DOCCATEGORYEFFECTIVE_UPDATED
129 AC 3/24/2004 1:54:29 PM
129 AC 4/2/2004 1:44:39 PM
129 AE 6/24/2004 8:49:13 AM
129 AU 9/28/2004 12:16:30 PM

I want to get the one record for AC with the 4/2 date, the one for AE and the one for AU. My script is only returning the AU record because it has the maximum date for Parent_Doc_No 129.

Quote:

Originally Posted by crackerbox

I have a table that I need to find the maximum date per Doc_No per Parent_Doccategory. I can get the maximum date per Doc_No but I can't get the information for the next level. The following script is getting the maximum date per airplane but I should get a maximum date each time the parent_doccategory changes. Can anyone help? This is the script so far:

CREATE PROCEDURE [dbo].[sp_Maximum_Change_Date] AS

select Effective_Updated , parent_doc_no, parent_doccategory from gdb_01_4.dbo.aircraft_changes As S
where effective_updated = (select max(effective_updated)
from gdb_01_4_test.dbo.aircraft_changes where
parent_doc_no = S.parent_doc_no) group by parent_doc_no,parent_doccategory, effective_updated, order by parent_doc_no
GO

PARENT_DOC_NOPARENT_DOCCATEGORYEFFECTIVE_UPDATED
129 AC 3/24/2004 1:54:29 PM
129 AC 4/2/2004 1:44:39 PM
129 AE 6/24/2004 8:49:13 AM
129 AU 9/28/2004 12:16:30 PM

I want to get the one record for AC with the 4/2 date, the one for AE and the one for AU. My script is only returning the AU record because it has the maximum date for Parent_Doc_No 129.

Try adding another argument in the first where clause saying which one you want like example add

and parent_doccategory = 'AE'

before the group by clause|||

Quote:

Originally Posted by Taftheman

Try adding another argument in the first where clause saying which one you want like example add

and parent_doccategory = 'AE'

before the group by clause

That will work but then it eliminates the other two lines that I also need. I basinally need two group levels, one for the Parent_doc_No and the other for the Parent_DocCategory so that it first looks at the Parent_Doc_No and then at the Parent_Doc_Category and gets the most up to date for each category.