requete sql bizarre

salut, je voudrais faire une requete,mais helas je ne touve pas la solution :
j'ai 3 tables :
Incident (incident_id,incident_ref,incident_rmk)
Action (action_id,incident_id,action_rmk,date_actioned,usr_id)
User(user_id,user_n)

je voudrais faire une requete qui me donne, pour chaque "user", le numero incident_ref et incident_rmk de leur derniere action
(cad pour leur max date_actioned)

j'ecris quelque chose comme ca :

Code:

---

1 2 3 4

select usr_n,incident_ref,incident_rmk,max(date_actioned) from action inner join incident on action.incident_id= incident.incident_id inner join user on user_id = action.usr_id group by usr_n

---

error:
Msg 8120, Level 16, State 1, Line 31
Column 'incident.incident_ref' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

comment realiser cette requete
merci

Bonjour,

Tous les champs se trouvant dans ton SELECT doivent être dans la clause GROUP BY sauf ceux comportant des agrégations (AVG, COUNT...etc) ;)

NB: :tagcode:

Beaucoup d'informations sur la clause group by ici

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

select distinct u.[user_id], u.user_n,a.action_id,a.action_rmk, a.date_actioned,i.incident_id,i.incident_rmk from [action] a,incident i, [user] u, (select u.[user_id], u.user_n,max(a.date_actioned) date_actioned from [action] a,[user] u where a.[user_id] = u.[user_id] group by u.[user_id], u.user_n) tbl where u.[user_id] = tbl.[user_id] and u.user_n = tbl.user_n and a.date_actioned = tbl.date_actioned and u.[user_id] = a.[user_id] and a.incident_id = i.incident_id

---