Group by with joins

You probably want something like this:

SELECT h_v_charges.*, 
       last_v.last_version
FROM hist_versions_charges h_v_charges 
  JOIN (select proj_charge_id, 
               max(version) as last_version
        from hist_versions_charges 
        where proj_sous_projet_id = 2  
        group by proj_charge_id
  ) last_v  
  ON h_v_charges.version = last_v.last_version
 AND h_v_charges.proj_charge_id = last_v.proj_charge_id 
ORDER BY h_v_charges.proj_charge_id ASC;

A possibly (because no join is required) faster solution would be:

select *
from (
   select hvc.*, 
          row_number() over (partition by proj_charge_id order by version desc) as rn
   from hist_versions_charges as hvc
   where proj_sous_projet_id = 2  
) as hv
where rn = 1
order by hv.proj_charge_id ASC;

As Colin has pointed out, this can also be written as:

with hv as (
  select hvc.*, 
         row_number() over (partition by proj_charge_id order by version desc) as rn
  from hist_versions_charges as hvc
  where proj_sous_projet_id = 2  
) 
select *
from hv
where rn = 1
order by hv.proj_charge_id ASC;

Obviously, there can be multiple rows with the same value in table1.sri and / or in table2.seg_guid. That's the only logical explanation for the multiplied row count in your query result.

Which of both is impossible to tell from the information you provided. But the name "trucknetwork" indicates there should be many "street names" in the result, so your task seems contradictory at the outset.

My educated guess is that the multiplication happens in table1 (many start and end points of roads in the same truck network). There are various techniques to reduce to a single pick. The best one depends on information not in the question. Since you get so many result rows, a LATERAL join with LIMIT 1 is a hot contender. Be sure to support it with an index.

Since you want to join to exactly 1 row in the result for each row in trucknetwork, you have to define which row to pick from a set of multiple matches. More information we don't have. I pick an arbitrary winner.

And there is also the possibility that some of the rows in trucknetwork might find no matching row in table1 and/or table2 at all. In an ideal world, referential integrity would be enforced with FOREIGN KEY constraints. To make do with what we have, use LEFT [OUTER] JOIN to avoid eliminating trucknetwork rows (which would probably indicate data errors in table1 or table2).

So:

CREATE TABLE network_ex AS
SELECT shp.descriptio AS descr, shp.road_type, shp.geom, t2.s_name AS street_name 
FROM   trucknetwork shp
LEFT   JOIN LATERAL (
   SELECT seg_guid
   FROM   table1
   WHERE  sri = shp.sri
   -- ORDER  BY ???      -- define which one to pick if that matters
   LIMIT  1              -- ! just the one !
   ) t1 ON true
LEFT   JOIN table2 t2 USING (seg_guid);

Ideally, you would have these two multicolumn indexes to make this fast:

CREATE INDEX table1_sri_seg_guid_idx ON table1 (sri, seg_guid);
CREATE INDEX table2_seg_guid_s_name_idx ON table2 (seg_guid, s_name);

If there can be duplication in table2 (as well), reduce to a single match in a similar fashion. Again, the best technique depends on the complete picture.

Alternatively, you could aggregate all (distinct) road names into a single string or array in the result ...

Related:

• What is the difference between LATERAL and a subquery in PostgreSQL?
• Optimize GROUP BY query to retrieve latest record per user

Asides:

• Use CREATE TABLE AS. SELECT INTO is discouraged:
  • Creating temporary tables in SQL
• Avoid unquoted CaMeL-case identifiers in Postgres.
  • Are PostgreSQL column names case-sensitive?