0

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!

GMB
  • 216,147
  • 25
  • 84
  • 135
Priyanka S. Desai
  • 67
  • 1
  • 10
  • Please, read [how to create a minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) and add some sample data, table structures and exact error message, because no one can guess. – astentx Nov 02 '20 at 08:18
  • "but getting an error" - What error? There are at least two problems I see in this query - one is the extensive usage of keywords as identifiers, and the other is that `DateTime` appears in the `order by` clause but not in the `group by` clause (Which is probably the reason you're getting an error). – Zohar Peled Nov 02 '20 at 08:24
  • Please read the guidelines on the [sql tag info](https://stackoverflow.com/tags/sql/info) and [edit] your question accordingly. – Zohar Peled Nov 02 '20 at 08:36

1 Answers1

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
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
GMB
  • 216,147
  • 25
  • 84
  • 135