Oui, la colonne RN, c'était pour moi pour voir
Et c'est vrai pour le distinct.

Le requête final/complète donne :

Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
SELECT s1.id, s1.titre, s1.joueur, s1.niveau, s1.description, s1.id_societe, s1.etat, s1.type, 
            s2.nom, s2.adresse, s2.cp, s2.ville, s2.pays, s2.website, s2.etat as etat_societe, AVG(a1.note_escape) as note_escape, AVG(a1.note_ambiance) as note_ambiance, 
            t2.path as img_path, t2.name as img_name, d1.id_scenario, d1.youtube, d1.duree, d1.multi_level, d1.tx_fouille, d1.tx_manipulation, d1.tx_reflexion, d1.tx_sortie, d1.handicap, d1.salle, d1.lg, d1.age_min, d1.remarque  
            FROM scenario AS s1 
            INNER JOIN societe AS s2 ON (s1.id_societe = s2.id) 
            LEFT JOIN avis AS a1 ON (s1.id=a1.id_scenario) 
            LEFT JOIN scenario_detail AS d1 ON (s1.id = d1.id_scenario) 
            LEFT JOIN (
				SELECT i1.path, i1.name, f2.id_scenario , 
				row_number() over(partition by id_scenario order by id asc) as RN
 
				FROM file AS i1
				INNER JOIN scenario_img AS f2 ON (i1.id_file=f2.id_file)	  
				WHERE f2.id_scenario IN (
					SELECT id
					FROM scenario
					WHERE scenario.id = f2.id_scenario
					)			
 
			) t2 
            ON t2.id_scenario = s1.id 
            WHERE
			coalesce(t2.RN, 1)=1
            GROUP BY s1.id, s1.titre, s1.joueur, s1.niveau, s1.description, s1.id_societe, s1.etat, s1.type, s2.nom, s2.adresse, s2.cp, s2.ville, s2.pays, s2.website, s2.etat, t2.path, t2.name, d1.id_scenario, d1.youtube, d1.duree, d1.multi_level, d1.tx_fouille, d1.tx_manipulation, d1.tx_reflexion, d1.tx_sortie, d1.handicap, d1.salle, d1.lg, d1.age_min, d1.remarque

Merci