CODEWITHSUNDEEP
sqlpostgresql
Filipe Dias
Filipe Dias

Reputation: 284

Select mininum value after grouping by two columns

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.

enter image description here

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;

Upvotes: 0

Views: 31

Answers (1)

Kurt
Kurt

Reputation: 1748

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

Upvotes: 1

Related Questions