Swapna Swapna - 9 days ago 5
SQL Question

How to get top 1 record of each group order by desc in SQL Server using linq

I need to get top 1 record order by

updatedtime
(latest) desc. I'm unable to filer top 1 record from my table

select watertowerid, DOValue, PHValue, WaterTemperature, CurrentTime
from ParkingSlots
group by watertowerid, PHValue, DOValue, WaterTemperature, CurrentTime


Sample data:

PHValue DOValue WaterTemperature watertowerid CurrentTime
---------------------------------------------------------------------------
3.00 4.00 22.00 1 2016-09-29 02:34:00
6.00 4.00 33.00 2 2016-11-29 02:34:00
8.22 6.55 28.22 1 2016-06-25 01:25:00
30.52 5.60 27.00 2 2016-08-29 02:34:00


Desired output:

PHValue DOValue WaterTemperature watertowerid CurrentTime
---------------------------------------------------------------------------
3.00 4.00 22.00 1 2016-09-29 02:34:00
6.00 4.00 33.00 2 2016-11-29 02:34:00

Answer

Use WITH TIES

select TOP(1) with ties *
from ParkingSlots 
order by row_number() over(partition by watertowerid order by CurrentTime DESC);
Comments