Maximum Date in a group of records

[crackerbox]

I have a table that I need to find the maximum date per Doc_No per Parent_Doccateg ory. 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_doccateg ory changes. Can anyone help? This is the script so far:

CREATE PROCEDURE [dbo].[sp_Maximum_Chan ge_Date] AS

select Effective_Updat ed , parent_doc_no, parent_doccateg ory from gdb_01_4.dbo.ai rcraft_changes As S
where effective_updat ed = (select max(effective_u pdated)
from gdb_01_4_test.d bo.aircraft_cha nges where
parent_doc_no = S.parent_doc_no ) group by parent_doc_no,p arent_doccatego ry, effective_updat ed, order by parent_doc_no
GO

PARENT_DOC_NO PARENT_DOCCATEG ORY EFFECTIVE_UPDAT ED
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.

[Taftheman]

I have a table that I need to find the maximum date per Doc_No per Parent_Doccateg ory. 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_doccateg ory changes. Can anyone help? This is the script so far:

CREATE PROCEDURE [dbo].[sp_Maximum_Chan ge_Date] AS

select Effective_Updat ed , parent_doc_no, parent_doccateg ory from gdb_01_4.dbo.ai rcraft_changes As S
where effective_updat ed = (select max(effective_u pdated)
from gdb_01_4_test.d bo.aircraft_cha nges where
parent_doc_no = S.parent_doc_no ) group by parent_doc_no,p arent_doccatego ry, effective_updat ed, order by parent_doc_no
GO

PARENT_DOC_NO PARENT_DOCCATEG ORY EFFECTIVE_UPDAT ED
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_doccateg ory = 'AE'

before the group by clause

[crackerbox]

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

and parent_doccateg ory = '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_DocCateg ory so that it first looks at the Parent_Doc_No and then at the Parent_Doc_Cate gory and gets the most up to date for each category.