selecting a column based on a minimum value of another column

Question

I have the following table.

test_type |  brand  | model  | band | firmware_version | avg_throughput
-----------+---------+--------+------+-----------------+----------------
 1client   | Linksys | N600   | 5ghz | 1               |          66.94
 1client   | Linksys | N600   | 5ghz | 2               |          94.98
 1client   | Linksys | N600   | 5ghz | 4               |         132.40
 1client   | Linksys | EA6500 | 5ghz | 1               |         216.46
 1client   | Linksys | EA6500 | 5ghz | 2               |         176.79
 1client   | Linksys | EA6500 | 5ghz | 4               |         191.44

I'd like to select the avg_throughput of each model that has the lowest firmware_version.

When I do SELECT test_type, model, min(firmware_version) FORM temp_table GROUP BY test_type, model I get what I want but once I add the avg_throughput column it requires me to also add it to the GROUP BY clause which makes it return all the rows when all I need is only the avg_throughput for the lowest firmware_version for each model type.

Answer 1

In standard SQL this can be done using a window function

select test_type, model, firmware_version, avg_throughput
from (
  select test_type, model, firmware_version, avg_throughput, 
         min(firmware_version) over (partition by test_type, model) as min_firmware
  from temp_table
) t
where firmware_version = min_firmware;

Postgres however has the distinct on operator which is usually faster than the corresponding solution with a window function:

select distinct on (test_type, model) 
       test_type, model, firmware_version, avg_throughput
from temp_table
order by test_type, model, firmware_version;

SQLFiddle example: http://sqlfiddle.com/#!15/563bd/1