I am working with a French Real Estate database in which all the sales in France are recorded per semester. It has 3 Tables:
- Localite, which has city (Commune) and state (Departement) data,
- Bien, which has house data (m², address, etc.) and is only used in this query because the way the tables are linked, and
- Mutation, which has price (ValeurFonciere) data.
Which were created using:
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;
I need to find the 3 cities with the highest average sales price, but for only a few states.
When I use this:
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;
I get the Top 3 across all 5 Departements selected (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.
But how do I get the top 3 for each of those Departements (so, 15 Commune in total)?
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.
Any help would be greatly appreciated.