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

Looks like someone has asked almost the same question here: [SQL Select only rows with Max Value on a Column][1] [1]: http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column — Tommy Ivarsson, May 19 '14 at 18:49
Well, you added to many columns to your select, and are also grouping speed, that doesn't make sense in this case. Do you need all the other columns? — Lamak, May 19 '14 at 18:53
Then why didn't you add them in the question when you asked it? — Lamak, May 19 '14 at 18:54
I didn't know that the number of columns I need was related to selecting the highest record against each asset. — Dawood Awan, May 19 '14 at 18:55

score 5 · Answer 1 · answered May 19 '14 at 18:49

5

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

answered May 19 '14 at 18:49

Jerrad

5,240
1
18
23

I am still getting the same result. I am posting my SQL in the question hope you can help me. – Dawood Awan May 19 '14 at 18:51
@DawoodAwan . . . Try `group by vm.iAssetId`. You don't want to group by the columns used in aggregation functions (except in some very rare circumstances). – Gordon Linoff May 19 '14 at 18:53
If I don't add group by to the other columns the script doesn't execute – Dawood Awan May 19 '14 at 18:54
You shouldn't be grouping by vm.fSpeedKPH. – Jerrad May 19 '14 at 18:55

M.Ali · Accepted Answer · 2014-05-19T18:56:23.727

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

edited May 19 '14 at 18:56

answered May 19 '14 at 18:48

M.Ali

67,945
13
101
127

Thanks. Seems to be working. By the way I didn't downvote your answer at first/ – Dawood Awan May 19 '14 at 19:02
No worries bro there are some clowns out there doesnt bother me anyway :) anyway glad to help :) – M.Ali May 19 '14 at 19:03

Select Highest value against each record in SQL

2 Answers2