Show most recent records

Z

ziobe

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!
 
S

Smartin

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);
 
Z

ziobe

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
 
J

John Spencer

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
 
S

Smartin

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
 
W

Wood

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...
 
Top