SQL query help [closed]

It is not necessary to use subqueries (but you can use them too):

SELECT  p.dni,
        p.nom_persona,
        p.cognoms_persona,
        cp.carrer,
        cp.metres_c
FROM CasaParticular AS cp
INNER JOIN Persona AS p
    ON cp.dni_cp = p.dni
ORDER BY cp.metres_c DESC
FETCH FIRST 1 ROWS WITH TIES;

Update

If you want to use subqueries (although I do not see the reason, because it will give you worse performance), then you can use the following code:

SELECT  p.dni,
        p.nom_persona,
        p.cognoms_persona,
        cp.carrer,
        cp.metres_c
FROM (  SELECT MAX(metres_c) Max_metres_c
        FROM CasaParticular) AS mcp
INNER JOIN CasaParticular AS cp
    ON cp.metrec_c = mcp.Max_metres_c
INNER JOIN Persona AS p
    ON cp.dni_cp = p.dni
;