title: How to get top 3 averages with a WHERE clause?
body: <p>I am working with a French Real Estate database in which all the sales in France are recorded per semester. It has 3 Tables:</p>
  <ol>
  <li>Localite, which has city (Commune) and state (Departement) data,</li>
  <li>Bien, which has house data (m², address, etc.) and is only used in this query because the way the tables are linked, and</li>
  <li>Mutation, which has price (ValeurFonciere) data.</li>
  </ol>
  <p>Which were created using:</p>
  <pre><code>CREATE TABLE Localite (
  Commune_ID SMALLINT UNSIGNED NOT NULL,
  Commune VARCHAR(50) NOT NULL,
  Departement VARCHAR(3) NOT NULL,
  PRIMARY KEY (Commune_ID)
  )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  
  CREATE TABLE Bien (
  Bien_ID SMALLINT UNSIGNED NOT NULL,
  SurCar1 FLOAT NOT NULL,
  TypeLoc VARCHAR(15) NOT NULL,
  NoPP SMALLINT NOT NULL,
  NoVoie VARCHAR(5) NOT NULL,
  TypeVoie VARCHAR(10) NOT NULL,
  NomVoie VARCHAR(50) NOT NULL,
  CodePostal VARCHAR(5) NOT NULL,
  Commune_ID SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (Bien_ID)
  )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  
  CREATE TABLE Mutation (
  Mutation_ID SMALLINT UNSIGNED NOT NULL,
  DateMutation DATETIME NOT NULL,
  ValeurFonciere INT UNSIGNED NOT NULL,
  Bien_ID SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (Mutation_ID)
  )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  </code></pre>
  <p><a href="https://i.stack.imgur.com/5TSN9.jpg" rel="nofollow noreferrer">Example Mutation Data</a>
  <a href="https://i.stack.imgur.com/rzNW1.jpg" rel="nofollow noreferrer">Example Localite Data</a></p>
  <p>I need to find the 3 cities with the highest average sales price, but for only a few states.</p>
  <p>When I use this:</p>
  <pre><code>SELECT Departement, Commune, avg(ValeurFonciere) as Prix_Moy
  FROM localite, bien, mutation
  where mutation.Bien_ID = bien.Bien_ID
  And bien.Bien_ID = localite.Commune_ID
  AND Departement in (6, 13, 33, 59, 69) 
  GROUP BY Commune
  ORDER BY Commune DESC
  limit 3;
  </code></pre>
  <p>I get the Top 3 across all 5 Departements (i.e. 6, 13, 33, 59, 69).
  In France each Departement (State) has its own identifying number, so those numbers are the Departements I am interested in.</p>
  <p>But how do I get the top 3 for each of those Departements (so, 15 Commune in total)?</p>
  <p>I have looked on here and have found several solutions using a rank() function with a partition, but I don't know if that's the correct solution here because I can't tell it to look at only the 5 Departements I need.</p>
  <p>Any help would be greatly appreciated.</p>