CODEWITHSUNDEEP
sqlgroup-bymaxcascading
highpoint
highpoint

Reputation: 21

Select latest month record from group of records for several groups

I have a table that generates a record each month for each project status report (PSR) if a PSR is/was generated. From a single table, I need to get the most recent record for each PSR.

This gives me the list of PSR's I am looking for:

SELECT Max(Main.PSRMonth) AS MaxOfPSRMonth, Main.PE, Main.Loc, Main.EWO
FROM Main
GROUP BY Main.PE, Main.Loc, Main.EWO
ORDER BY Main.PE, Main.Loc, Main.EWO;

Table:Main 
Primary Key is PSRMonth + PE + EWO + LOC

Now, I need all fields FROM Main based on the selection above. I would like to do this with one SQL statement.

Upvotes: 2

Views: 121

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

I need all fields FROM Main based on the selection above.

Then try this:

SELECT m1.*
FROM Main m1
INNER JOIN 
(
    SELECT 
      Max(PSRMonth) AS MaxOfPSRMonth, 
      PE, 
      Loc, 
      EWO
    FROM Main 
    GROUP BY PE, Loc, EWO 
) m2 ON  m1.PE = m2.PE AND m1.Loc = m2.Loc AND m1.EWO = m2.EWO 
     AND m1.PSRMonth = m2.MaxOfPSRMonth
ORDER BY m1.PE, M1.Loc, M1.EWO;

Upvotes: 1

Related Questions