Select Highest value against each record in SQL

Question

I am new to SQL and the problem I am having is that I have the value for alot assets in a table. I need to get the highest speed for each asset in that table.

I have tried searching google but I found the MAX() function of SQL.

I don't need the MAX() because that will only give me one record with the highest value. I need the highest for each asset:

e.g.

iAssetId fSpeedKPH

1 78

5 77

5 80

8 74

8 81

8 88

8 111

24 71

24 78

24 79

24 79

24 82

24 84

24 90

24 91

24 92

I have highlighted the highest row for each asset i.e. AssetId = 1, 5, 24 and 8

These are the rows I need to select.

What is the most efficient way?

Do I have to loop through this result-set returned by the SQL I have written?

EDIT:

My SQL:

DECLARE @dateMinusDay datetime = DateAdd(dd, -1, GetDate())

select vm.iAssetId, max(vm.fSpeedKPH), vm.dtUTCDateTime, ge.sGeofenceName  from VehicleMonitoringLog vm
inner join Geofences ge on ge.iGeofenceId = vm.iGeofenceId

where vm.iGeofenceId != 1 AND vm.fSpeedKPH > 70 AND (vm.dtUTCDateTime > @dateMinusDay AND vm.dtUTCDateTime < GETDATE())

group by
vm.iAssetId,vm.fSpeedKPH, vm.dtUTCDateTime, ge.sGeofenceName

Answer 1

SELECT iAssetId, fSpeedKPH
FROM (
SELECT iAssetId, fSpeedKPH
       ,ROW_NUMBER() OVER (PARTITION BY iAssetId ORDER BY fSpeedKPH DESC) AS RN
FROM Table_Name )Sub
WHERE RN = 1

UPDATE

DECLARE @dateMinusDay datetime = DateAdd(dd, -1, GetDate())

SELECT Q.iAssetId, Q.dtUTCDateTime, Q.sGeofenceName
FROM (
select vm.iAssetId
      , vm.dtUTCDateTime
      , ge.sGeofenceName  
      ,ROW_NUMBER() OVER (PARTITION BY vm.iAssetId ORDER BY vm.fSpeedKPH DESC) AS RN
from VehicleMonitoringLog vm inner join Geofences ge 
on ge.iGeofenceId = vm.iGeofenceId
where vm.iGeofenceId != 1 AND vm.fSpeedKPH > 70 
AND (vm.dtUTCDateTime > @dateMinusDay   --<-- Instead of variable you can use GETDATE() - 1
      AND vm.dtUTCDateTime < GETDATE())
      )Q
WHERE RN = 1

Answer 2

select iAssetId, max(fSpeedKPH)
from AssetsTable
group by iAssetId