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.

user330315 · Accepted Answer · 2014-08-12 18:20:41Z

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

I got super excited when I learned about "distinct on" here, and then found out Redshift doesn't seem to support it :(

jeffam217 · Accepted Answer · 2014-08-12 18:22:13Z

This should be what you're looking for if I'm reading your post correctly, and I think it's a pretty easily readable way of doing it. :-)

WITH min_firmware_version (model, firmware_version)
AS
(
    SELECT
        model,
        MIN(firmware_version)
    FROM temp_table
    GROUP BY
        model
)
SELECT
    temp_table.model,
    temp_table.firmware_version,
    temp_table.avg_throughput
FROM temp_table
INNER JOIN min_firmware_version
    ON temp_table.model = min_firmware_version.model
    AND temp_table.firmware_version = min_firmware_version.firmware_version

beeef · Accepted Answer · 2014-08-12 17:27:47Z

0

I think you need that SQL statement:

SELECT t.test_type, t.model, t.firmware_version, t.avg_throughput 
FROM temp_table t 
WHERE t.firmware_version = (SELECT min(firmware_version) FROM temp_table)

answered Aug 12, 2014 at 17:27

beeef

2,7124 gold badges20 silver badges28 bronze badges

2 Comments

user330315 Over a year ago

You need a co-related subquery because otherwise you get the overal min(firmware_version) not the one for the model from the outer query

sylvian Over a year ago

That's close but it selects the minimum version for all models but what if the minimum version is different for each model? Sorry the example table above is not very clear in this.

Collectives™ on Stack Overflow

selecting a column based on a minimum value of another column

3 Answers 3

1 Comment

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Linked

Related