1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
| SELECT t3.Ville, t3.NumPropC, t3.nb_parcelles
FROM
(
SELECT t1.Ville, MAX(t1.nb_parcelles) AS nb_parcelles_max
FROM
(
SELECT p.Ville, p.NumPropC, COUNT(pa.IdParcelle] AS nb_parcelles
FROM Proprietaire p
INNER JOIN Parcelle pa ON pa.NumPropC = p.NumPropC
GROUP BY p.Ville, p.NumPropC
) t1
GROUP BY t1.Ville
) t2
INNER JOIN
(
SELECT p.Ville, p.NumPropC, COUNT(pa.IdParcelle) AS nb_parcelles
FROM Proprietaire p
INNER JOIN Parcelle pa ON pa.NumPropC = p.NumPropC
GROUP BY p.Ville, p.NumPropC
)t3
ON t3.Ville = t2.Ville
AND t3.nb_parcelles = t2.nb_parcelles_max
ORDER BY t3.Ville |
Partager