How to write query to get 1st entry of each group in SQL Server?

Question

I want to get 1st odometer value of each vehicle for each date. I have tried this for getting a start Odometer but getting an error:

SELECT TOP 1 
    CAST(DateTime AS DATE) AS Date, RegistrationNo, OdoMeter AS StartOdometer 
FROM 
    EventsData 
GROUP BY 
    RegistrationNo, CAST(DateTime AS DATE) 
ORDER BY 
    RegistrationNo, DateTime

I am getting this error:

[SQL Server]Column 'EventsData.OdoMeter' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (8120) (SQLExecDirectW)")

What is the right way? Please help!

Answer 1

I want to get 1st odometer value of each vehicle for each date.

That's a typical greatest-n-per-group problem. One option uses window functions:

select *
from(
    select ed.*, 
        row_number() over(partition by registrationno, convert(date, datetime) order by datetime) rn
    from eventsdata ed
) ed
where rn = 1
order by registrationno, datetime