Add highest value as a column using window function

Question

I have a table as follows:

Query to copy data:

DROP TABLE IF EXISTS units_table;
CREATE TEMP TABLE units_table
(
 Brand varchar(100),
 units numeric(38,12)
);


  INSERT INTO units_table (Brand, units)
   VALUES ('A',200),('B',0),('C',300),('D',400),('E',1500),('F',700),('G',800),('H',450);

Specially using window functions, I want to get the highest value in the table. As follows:

But when I use:

select

brand,
units,
FIRST_VALUE(units) OVER () as Highest

from units_table

The first value it gives is 0. If I do this,

select

brand,
units,
FIRST_VALUE(units) OVER (ORDER BY UNITS) as Highest

from units_table

Throw an error. How specifically should Window Function be used to solve this problem? Thank you in advance!

Answer 1

select brand,units,
 FIRST_VALUE(units) OVER (ORDER BY UNITS DESC) as Highest
from units_table

Useful to me

Answer 2

You needMAX()Window function:

SELECT brand,
       units,
       MAX(units) OVER () AS Highest
FROM units_table;

See Demo.