Show most recent records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

Please help, I've the data from a table as follow:

Project_ID Phase Funding
1 2 45
1 3 34
2 5 78
2 6 85
3 4 69
3 5 9

How do I create a query to display only the most recent record base on Max
of Phase?

Thanks in advance!
 
ziobe said:
Hello,

Please help, I've the data from a table as follow:

Project_ID Phase Funding
1 2 45
1 3 34
2 5 78
2 6 85
3 4 69
3 5 9

How do I create a query to display only the most recent record base on Max
of Phase?

Thanks in advance!
Click to expand...

You don't give any hint about how to determine the "most recent record",
but if you are only concerned about "Max of Phase" this seems to work:

SELECT TOP 1 MyTable.*
FROM MyTable
ORDER BY MyTable.Phase DESC;

Project_ID Phase Funding
2 6 85


The following also works:

SELECT * FROM MyTable
WHERE PHASE IN
(SELECT MAX (PHASE) FROM MyTable AS T2);
 
Thanks for the quick reply..... I would like to display max phase per project
ID as well as the funding.

The expected result set are as follow:

Project_ID Phase Funding
1 3 34
2 6 85
3 5 9
 
Assuming that your naming conventions for tables and fields don't use spaces
or special characters, the most efficient SQL I can think of is the
following.

SELECT T.Project_ID, T.Phase, T.Funding
FROM YourTable as T INNER JOIN
(SELECT Project_ID, Max(Phase) as MaxPhase
FROM YourTable As T2) as T3
ON T.Project_ID = T3.Project_ID
AND T.Phase = T3.MaxPhase
 
PMFJI,

the missing link is likely thus:

SELECT T.Project_ID, T.Phase, T.Funding
FROM YourTable as T INNER JOIN
(SELECT Project_ID, Max(Phase) as MaxPhase
FROM YourTable As T2

GROUP BY T2.Project_ID

) as T3
ON T.Project_ID = T3.Project_ID
AND T.Phase = T3.MaxPhase

HTH
 
It works! Thank you very very much.

WH

Smartin said:
PMFJI,

the missing link is likely thus:

SELECT T.Project_ID, T.Phase, T.Funding
FROM YourTable as T INNER JOIN
(SELECT Project_ID, Max(Phase) as MaxPhase
FROM YourTable As T2

GROUP BY T2.Project_ID

) as T3
ON T.Project_ID = T3.Project_ID
AND T.Phase = T3.MaxPhase

HTH
Click to expand...
 
Back
Top