I am trying to apply a definition query to an AVL table (on an enterprise geodatabase - Microsoft SQL Server 2016) that returns the most recent record for each vehicle in the table. After a little research, I decided to use the TOP method which requires an ORDER BY statement. I've also included every field in the GROUP BY statement so that they can be included in the resulting table (per an error that I encountered).

Current SQL Query:

WHERE VGROUP <> 'Engineering' AND VGROUP <> 'Resource Mgt'

The root of the issue is this: even when I remove "TOP(1)" from the SELECT statement, the resulting table is empty. This indicates, to me, that there is an non-syntax error somewhere else in the statement that I am unable to identify.

The error mentioned above can be ignored as after testing the code again, the error was not flagged a second time. Originally, the error stated that I could not include fields in the SELECT statement that were not also included in an aggregate function or a GROUP BY statement.

  • 2
    You need to describe this error. I see no need for a GROUP BY in your post, usually that is for aggregate functions like SUM or AVG. – Zack May 10 '18 at 16:22
  • I have amended the original posting - I was unable to reproduce the error after testing it again. However, I believe I at least need to GROUP BY VEHID so that I get the top-most record for each vehicle rather than the top-most record in the table. – Have_Z May 10 '18 at 16:52
  • 1
    No. TOP is to get the first record and ORDER BY ID ASC will give you the lowest id at the top of the list so you'll retrieve only one record with these two combined. I'd reconsider how you're ordering right now though. he error that you describe occurs when you're doing aggregation and then attempting to retrieve a field that you never handled in that aggregation. Seeing as you have no agg in your post, I surmise that you had one, got that err, then removed it and no longer need to worry about grouping. – Zack May 10 '18 at 17:39
  • Use this resource w3schools.com/sql/sql_top.asp – Zack May 10 '18 at 17:40
  • 1
    Thank you for that information. I'll certainly be referring to that site for future programming issues and education. If I'm understanding this correctly, I'd get one record from the query with or without the GROUP BY VEHID statement? Does the GROUP BY clause have anything to do with the resulting table being empty though? That's the issue I'm most confused about here. Assuming my syntax is correct, I had thought that there would be at least one record in the resulting table. – Have_Z May 10 '18 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.