0

I have the most frustrating error that keeps coming up about an item not in the Group By Clause.

Column 'Task' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I have read multiple examples, but they don't appear to address my issue. Any assistance with this would be greatly appreciated.

I have a table of Tasks - listing the JobId, the Task and the Date the task was completed, as follows:

JobId   Task    TaskCompleted
1       A       01/01/2022
1       B       02/01/2022
1       C       03/01/2022
1       D
2       A       02/01/2022
2       B       04/01/2022
2       C   
3       A       05/01/2022
3       B       07/01/2022

I am after a list of the most recent completed task for each JobId. So the result I am after is as follows:

Job     LastTask    CompletedDate
1       C       03/01/2022
2       B       04/01/2022
3       B       07/01/2022

I would have expected the following would work:

select  JobId as Job, Task as LastTask, max(TaskCompleted) as CompletedDate
from    TaskTable
where   
    TaskCompleted is not null
group by JobId  --, Task

However I get the Group By Clause error. If I add the "Task" column to the Group by I end up getting multiple entries per job (1 entry for each task)

I've read up on this but can't seem to solve it.

If you can assist with this it would be greatly appreciated.

gfsadmin
  • 11
  • 4
  • There is no `TaskDate` in your sample data, but there is in your WHERE clause in your query. Posting made-up code introduces errors. When asking a question, post your **actual code**. – Ken White Oct 21 '22 at 23:09
  • 1
    It sounds like you need to address this as a "select the last record in a group" problem. There are many similar questions that you can find [with this search](https://stackoverflow.com/search?q=%5Bsql-server%5D+Last+record+in+a+group). In particular, [this one](https://stackoverflow.com/questions/4751913/retrieving-last-record-in-each-group-from-database-sql-server-2005-2008) may contain your answer. – T N Oct 21 '22 at 23:10
  • Sorry updated the sample code. Also I am not after the last record, I am after the record with the most recent date, but i want to show the Task Name. This is the sticky point, correctly showing the Task Name for the last record. – gfsadmin Oct 21 '22 at 23:23
  • The reason you haven't found an answer is because you're not grouping... grouping implies aggregating. And you might not want the "last" record of the group, but you want a specific row from a group, which the duplicate does address, you just need to modify the row__number criteria to meet your needs. – Dale K Oct 21 '22 at 23:31
  • And you still reference that TaskDate without showing a task date. – Dale K Oct 21 '22 at 23:32
  • Thanks @DaleK - but not sure what you mean by modifying the "row_number" criteria ? – gfsadmin Oct 21 '22 at 23:50
  • If you check the linked solution, it generates a row number to work out which rows to keep. So modify that logic to meet your requirements. Take the time to understand how it works and you will have another tool in your toolbox rather than needing us to solve it for you. – Dale K Oct 22 '22 at 00:15

0 Answers0