Select mininum value after grouping by two columns-CodePudding

The following table contains two grouping variables, id_point and sci_name and a continuous variable distance. There are multiple distance values per unique combinations of id_point and sci_name. I would like to create a table that contains each unique combination between id_point and sci_name and the minimum distance value.

This was my best attempt:

CREATE TABLE sar.test AS
SELECT DISTINCT id_point, sci_name, MIN(distance)
GROUP BY id_point, sci_name,
    FROM sar.dist_mam_na;

CodePudding user response：

The GROUP BY goes after the FROM, and by definition of aggregation, the groups will be unique so the DISTINCT is irrelevant:

SELECT id_point, sci_name, MIN(distance)
FROM sar.dist_mam_na
GROUP BY id_point, sci_name