-1

I have a problem, I need to show unique data from some table, sql server based, ihave the query but I have a error if not all column I select is not contain in the GROUP BY.

Table source:

+------+------+----------+-------+
| code | name | process  | time  |
+------+------+----------+-------+
| a    | qq   | write    | 10:10 |
| b    | qq   | read     | 11:10 |
| c    | qq   | read     | 12:11 |
| a    | qq   | write    | 09:40 |
| b    | yy   | write    | 08:50 |
| a    | yy   | read     | 11:10 |
| b    | yy   | write    | 09:40 |
+------+------+----------+-------+

Table I need:

+------+------+----------+-------+
| code | name | process  | time  |
+------+------+----------+-------+
| a    | qq   | write    | 10:10 |
| b    | qq   | read     | 11:10 |
| b    | yy   | write    | 09:40 |
| c    | qq   | read     | 12:11 |
| a    | yy   | read     | 11:10 |
+------+------+----------+-------+

Code I use:

select code, name, process, time 
from tablesource 
group by code, name, process;

But this query result in error:

[42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Column 'time' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (8120),

But if column time I insert in group by, the result is just same as the table source. Please help me.

Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
cadmad
  • 45
  • 1
  • 6
  • 1
    When you have two different timestamps, for a group of code, name, process, which timestamp do you need the query to return ? – MikNiller Jan 03 '20 at 08:28
  • 3
    Does this answer your question? [Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause](https://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e) – Amira Bedhiafi Jan 03 '20 at 08:59

1 Answers1

2

You need to modify query to add aggregate function for time.

select code, name, process, max(time) from tablesource 
group by code, name, process;
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58