How to group by incuding UID column in the new table

I ‘m trying to group some data with the following query but I need also to add the uid column from a_import.veg table in the c_schema.worst_case table which contains unique values. As far as I know I can’t add it as usual cause group by doesn’t work properly as it takes all the unique ids in the new table… Is there any way to do this?

CREATE TABLE IF NOT EXISTS c_schema.worst_case
as SELECT back_str, ahead_str, substr(weather_case, 1, 4), min(min_dist) as min_dist
FROM a_import.veg
GROUP BY back_str, ahead_str, substr(weather_case, 1, 4)
ORDER BY back_str, ahead_str;

Answer

I am guessing you want distinct on:

SELECT DISTINCT ON (back_str, ahead_str, substr(weather_case, 1, 4)) v.*
FROM a_import.veg v
ORDER BY back_str, ahead_str, substr(weather_case, 1, 4), min_dist;

(Select the columns you actually want.)

Actually, Postgres now supports LEFT(), so you can shorten this to:

SELECT DISTINCT ON (back_str, ahead_str, LEFT(weather_case, 4)) v.*
FROM a_import.veg v
ORDER BY back_str, ahead_str, LEFT(weather_case, 4), min_dist;