Query getting max number of one column with multiiple corresponing values in other column

Transact-SQL
1

I have a table that i would like to query that has two columns ECRNumber and Revision. The same ECRNumber could be used many times with different revision numbers aka 1, 2, 3, 4 I would like to query this table to list the ECRNumbers on a webpage with only one ECRNumber each and the Highest or last revision number created.
ECRNumber Revision
149000270 1
149000271 1
149000272 1
149000273 1 The query should remove this one
149000273 2 The query should remove this one
149000273 3 The query should remove this one
149000273 4
149000274 1
149000275 1
149000276 1
149000277 1
149000278 1
149000279 1
149000280 1
149000281 1
149000282 1
149000283 1
149000285 1
149000286 1

2

This is the query to pull the records above.
SELECT ECRNumber,Revision
FROM [MF].[dbo].[ECRs]
order by ECRNumber

3

Something like?

SELECT ECRNumber, MAX(Revision) AS Revision 
FROM MF.dbo.ECRs 
GROUP BY ECRNumber 
ORDER BY ECRNumber;
4

When I tried that it did not work for me except I had put group by ECRNUmber, Revision that that worked thank you.:slight_smile: