WINDOW FUNCTION FIRST_VALUE

description

FIRST_VALUE() returns the first value in the window’s range.

  1. FIRST_VALUE(expr) OVER(partition_by_clause order_by_clause [window_clause])

example

We have the following data

  1. select name, country, greeting from mail_merge;
  2. | name | country | greeting |
  3. |---------|---------|--------------|
  4. | Pete | USA | Hello |
  5. | John | USA | Hi |
  6. | Boris | Germany | Guten tag |
  7. | Michael | Germany | Guten morgen |
  8. | Bjorn | Sweden | Hej |
  9. | Mats | Sweden | Tja |

Use FIRST_VALUE() to group by country and return the value of the first greeting in each group:

  1. select country, name,
  2. first_value(greeting)
  3. over (partition by country order by name, greeting) as greeting from mail_merge;
  4. | country | name | greeting |
  5. |---------|---------|-----------|
  6. | Germany | Boris | Guten tag |
  7. | Germany | Michael | Guten tag |
  8. | Sweden | Bjorn | Hej |
  9. | Sweden | Mats | Hej |
  10. | USA | John | Hi |
  11. | USA | Pete | Hi |

keywords

  1. WINDOW,FUNCTION,FIRST_VALUE