Skip to main content
C
CF_test
Participant
August 30, 2010
Answered

SQL Query to retrieve the All records based on the Max Dates.

  • August 30, 2010
  • 2 replies
  • 639 views

Hello all,

I am trying to retrieve the newest record based on the date field (  nextDate  ).

Currently there are only 4 records in the MC_Maintenance table and two in the Machine table.

Machine table

MC_id             EquipID          

1                      0227

MC_id             EquipID

2                     0228

---------------------------------

MC_Maintenance table

Maint_id           MC_id             Next_maint                  

1                      2                      08/25/2010     

2                      2                      07/01/2010

3                      1                      06/11/2010

4                      1                      07/11/2010

What I  am trying to accomplish is,

list the two machines from the Machine table with the MAX(Next_maint) controlling the MC_Maintenance output list

These are the records that I would like to Display.

Maint_id           MC_id             Next_maint                  

1                      2                      08/25/2010

4                      1                      07/11/2010                 

Below is the SQL Query

SELECT

       MC.MC_ID as ID,

        MC.complete_Date as completed,

        MC.next_maint as nextDate,

        MC.maint_notes as Notes,

        MC.facility as Facility,

        M.EquipId,

        M.name as name,

        M.SerialNumber as SN,

        M.dept as dept,

        M.Freq as freq

        From  MC_Maintenance MC, Machine M

        where  MC.MC_ID =  M.MC_ID

'           USING MAX(nextDate )

Any ideas would help.

TJ

This topic has been closed for replies.
Correct answer ilssac

I would have thought that was a simple group by problem?

SELECT M.EquipID, MC.MC_ID, Max(MC.next_maint)
FROM MC_Maintenance MC INNER JOIN Machine M ON MC.MC_ID = M.MC_ID
GROUP BY M.EquipID, MC.MC_ID

2 replies

C
CF_testAuthor
Participant
August 30, 2010

Thanks,

Simple is best.  I guess I was getting to complex with the SQL.

Thanks

TJ

ilssac
ilssacCorrect answer
Inspiring
August 30, 2010

I would have thought that was a simple group by problem?

SELECT M.EquipID, MC.MC_ID, Max(MC.next_maint)
FROM MC_Maintenance MC INNER JOIN Machine M ON MC.MC_ID = M.MC_ID
GROUP BY M.EquipID, MC.MC_ID
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices