CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquerypartitioning
Norah Jones
Norah Jones

Reputation: 467

Get the lowest value per column based on another column

Per each product_id based on the column year, I would like to get the value from column name that would represent the earliest name.

year name product_id initial_name
2016 Pepsi. 123456 Pepsi.
2017 Pepsi co. 123456 Pepsi.
2017 Cola 567890 Cola
2018 Coca-Cola 567890 Cola
2019 Coca-Colas 567890 Cola

I started implementing the logic in this way but there must be an easier way:

SELECT *,
CASE WHEN year = date_min THEN name END name_min
FROM (
SELECT *,
    MIN(year) OVER (PARTITION BY product_id) year_min
FROM table

Upvotes: 0

Views: 317

Answers (1)

forpas
forpas

Reputation: 164214

Use FIRST_VALUE() window function:

SELECT *,
       FIRST_VALUE(name) OVER (PARTITION BY product_id ORDER BY year) name_min
FROM tablename;

Upvotes: 2

Related Questions