Select MAX record from a multi-table query

[elsenorjose]

Hello everyone,

I'm trying to write a query to help me retrieve the most recent Training Number for a course based on the most recent Completed Date for that course. So far, I have written the following:

  SELECT 
  MAX(V_ATH_ACT_TRNG_HISTORY.ATH_END_DATE)
, V_RCH_REQMENTS_COVERED.RCH_REQ_NO
, V_ATH_ACT_TRNG_HISTORY.ATH_TRAINING_NO
, V_ATH_ACT_TRNG_HISTORY.ATH_TRAINING_TITLE
, V_CUR_COURSE.DOT_TYPE
, V_ATH_ACT_TRNG_HISTORY.ATH_CREDIT_HOURS
, V_ATH_ACT_TRNG_HISTORY.ATH_TRN_PRIMARY_RESULT
, V_ATH_ACT_TRNG_HISTORY.ATH_SOURCE_TYPE
, V_RCH_REQMENTS_COVERED.RCH_REQ_TITLE
 FROM   (SmartProd.dbo.V_RCH_REQMENTS_COVERED V_RCH_REQMENTS_COVERED 
INNER JOIN SmartProd.dbo.V_ATH_ACT_TRNG_HISTORY V_ATH_ACT_TRNG_HISTORY 
ON V_RCH_REQMENTS_COVERED.RCH_ATH_ID=V_ATH_ACT_TRNG_HISTORY.ATH_ID) 
LEFT OUTER JOIN SmartProd.dbo.V_CUR_COURSE V_CUR_COURSE 
ON V_RCH_REQMENTS_COVERED.RCH_BASE_DOC_ID=V_CUR_COURSE.CUR_ID
GROUP BY V_RCH_REQMENTS_COVERED.RCH_REQ_NO
, V_ATH_ACT_TRNG_HISTORY.ATH_TRAINING_TITLE
, V_CUR_COURSE.DOT_TYPE
, V_ATH_ACT_TRNG_HISTORY.ATH_CREDIT_HOURS
, V_ATH_ACT_TRNG_HISTORY.ATH_TRN_PRIMARY_RESULT
, V_ATH_ACT_TRNG_HISTORY.ATH_SOURCE_TYPE
, V_ATH_ACT_TRNG_HISTORY.ATH_TRAINING_NO
, V_RCH_REQMENTS_COVERED.RCH_REQ_TITLE
ORDER BY RCH_REQ_NO, ATH_TRAINING_NO

The problem is I am still retrieving multiple records for each course/training number combination.

Here's some sample data as my query retrieves it:

12/08/10 AB-SHIFT-A-CUR-001 AB-SHIFT-TRN-0001 Shift A group training session for 04 May 10
12/09/10 AB-SHIFT-A-CUR-001 AB-SHIFT-TRN-0002 Shift A group training for 01 June 10
12/09/10 AB-SHIFT-A-CUR-001 AB-SHIFT-TRN-0003 Shift A group training 13 July 10
12/09/10 AB-SHIFT-A-CUR-001 AB-SHIFT-TRN-0004 Shift A group training for 10 Aug 10
12/09/10 AB-SHIFT-A-CUR-001 AB-SHIFT-TRN-0005 Shift A group training for 07 Dec 10
01/11/11 AB-SHIFT-A-CUR-001 AB-SHIFT-TRN-0046 Team A Shift Training Form for 04 Jan 11
02/08/11 AB-SHIFT-A-CUR-001 AB-SHIFT-TRN-0047 Team A Shift Training Form for 08 Feb 11
03/08/11 AB-SHIFT-A-CUR-001 AB-SHIFT-TRN-0048 Team Shift Training Form for 08 Mar 11

But what I want is:

03/08/11 AB-SHIFT-A-CUR-001 AB-SHIFT-TRN-0048 Team Shift Training Form for 08 Mar 11

Can someone please help me figure this out? I'm using SQL Server 2005.

Thank you

 

[djj55]

You should use a smaller query. You may need a sub query or cte to get the result you are looking for.

1) Find the max date you want
2) find the remaining information for that record

For step two there are many posts here and google that explain how to do it.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[imex]

Try something like that:

  SELECT 
  MAX(V_ATH_ACT_TRNG_HISTORY.ATH_END_DATE)
, V_RCH_REQMENTS_COVERED.RCH_REQ_NO
, V_ATH_ACT_TRNG_HISTORY.ATH_TRAINING_NO
, V_ATH_ACT_TRNG_HISTORY.ATH_TRAINING_TITLE
, V_CUR_COURSE.DOT_TYPE
, V_ATH_ACT_TRNG_HISTORY.ATH_CREDIT_HOURS
, V_ATH_ACT_TRNG_HISTORY.ATH_TRN_PRIMARY_RESULT
, V_ATH_ACT_TRNG_HISTORY.ATH_SOURCE_TYPE
, V_RCH_REQMENTS_COVERED.RCH_REQ_TITLE
 FROM   (SmartProd.dbo.V_RCH_REQMENTS_COVERED V_RCH_REQMENTS_COVERED 
INNER JOIN SmartProd.dbo.V_ATH_ACT_TRNG_HISTORY V_ATH_ACT_TRNG_HISTORY 
ON V_RCH_REQMENTS_COVERED.RCH_ATH_ID=V_ATH_ACT_TRNG_HISTORY.ATH_ID) 
LEFT OUTER JOIN SmartProd.dbo.V_CUR_COURSE V_CUR_COURSE 
ON V_RCH_REQMENTS_COVERED.RCH_BASE_DOC_ID=V_CUR_COURSE.CUR_ID
WHERE 
  V_ATH_ACT_TRNG_HISTORY.ATH_END_DATE = 
    (SELECT 
     MAX(ATH_END_DATE) 
     FROM SmartProd.dbo.V_ATH_ACT_TRNG_HISTORY SUBQRY
     WHERE SUBQRY.ATH_ID = V_RCH_REQMENTS_COVERED.RCH_ATH_ID)

I hope this helps.


Casas a venda em Suzano

 

[markros]

;with cte as (
SELECT 
  V_ATH_ACT_TRNG_HISTORY.ATH_END_DATE,

ROW_NUMBER() over (partition by V_RCH_REQMENTS_COVERED.RCH_BASE_DOC_ID 
ORDER BY V_ATH_ACT_TRNG_HISTORY.ATH_END_DATE DESC, 
V_ATH_ACT_TRNG_HISTORY.ATH_TRAINING_NO DESC) as Row,

, V_RCH_REQMENTS_COVERED.RCH_REQ_NO
, V_ATH_ACT_TRNG_HISTORY.ATH_TRAINING_NO
, V_ATH_ACT_TRNG_HISTORY.ATH_TRAINING_TITLE
, V_CUR_COURSE.DOT_TYPE
, V_ATH_ACT_TRNG_HISTORY.ATH_CREDIT_HOURS
, V_ATH_ACT_TRNG_HISTORY.ATH_TRN_PRIMARY_RESULT
, V_ATH_ACT_TRNG_HISTORY.ATH_SOURCE_TYPE
, V_RCH_REQMENTS_COVERED.RCH_REQ_TITLE
 FROM   (SmartProd.dbo.V_RCH_REQMENTS_COVERED V_RCH_REQMENTS_COVERED 
INNER JOIN SmartProd.dbo.V_ATH_ACT_TRNG_HISTORY V_ATH_ACT_TRNG_HISTORY 
ON V_RCH_REQMENTS_COVERED.RCH_ATH_ID=V_ATH_ACT_TRNG_HISTORY.ATH_ID) 
LEFT OUTER JOIN SmartProd.dbo.V_CUR_COURSE V_CUR_COURSE 
ON V_RCH_REQMENTS_COVERED.RCH_BASE_DOC_ID=V_CUR_COURSE.CUR_ID)

select * from cte where Row = 1

PluralSight Learning Library

 

[gbaughma]

I love the "WITH" clause.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com