0

This is my query :

  WITH TABLE as (
 SELECT distinct STJH.[META_CODE_ORIGINE] AS Salle_TMD_JOUR_HISTORIQUE_ID
,STJH.[STJH_DATE_ACTION]
,STJH.[STJH_TYPE_ACTION]
, STH.ST_ID,
 STJH.[STJ_JOUR]
,STJH.[STJ_HEURE_DEBUT]
,STJH.[STJ_HEURE_FIN]
,STH.[META_CODE_ORIGINE] AS SALLE_TMD_HISTORIQUE_ID
,STH.[ST_DATE_DEBUT]
,STH.[ST_DATE_FIN]
,STH.[STH_DATE_ACTION]
,MAX (sth.STH_DATE_ACTION) over (partition by STH.[META_CODE_ORIGINE]) as max_date_action, 
FROM [BI_EASILY].[bloc].[D_TMD_JOUR_HISTORIQUE] STJH
left JOIN [BI_EASILY].[bloc].[D_TMD_HISTORIQUE] STH ON STJH.[ST_ID] = STH.[ST_ID]
WHERE STJH.[STJH_TYPE_ACTION] <> 3 
 AND STJH.ST_ID = 37 and STJH.ST_ID = 37
 AND STJ_JOUR = 1 ) 
select * from table where table.max_date_action <= stjh_date_action

This is my result ( sorry i didn't copy manualy a lot of data)%My Result

I want to show only last row whose has max value of STJH_Date_Action. When i will remove my filter « Where STJ_JOUR =1 (Monday). I want to show only one row having STJ_JOUR_VALUE = 1. I tried to do this by this condition « where max_date_action< = STJH_DATE_ACTION » but it doesn’t work. Can you help me please.

Jakouza
  • 19
  • 4
  • 1
    Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Thom A Sep 13 '22 at 12:05
  • i was thinking about this solution. But i don't know if "PARTITION BY concerns STJH_DATE_ACTION or SALLE_TMD_JOUR_HISTORIQUE_ID ? – Jakouza Sep 13 '22 at 12:09

1 Answers1

0

for all the records in the result (max_date_action<=stjh_date_action) as you partition the data by (STH.[META_CODE_ORIGINE])

Mandana
  • 46
  • 3