add highes as a column using Window Functions-CodePudding

I have a table like below:

Query to replicate 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);

Using window functions specifically, I want to get the highest against the table. Like below:

However when I use:

select

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

from units_table

Its gives the first value as 0. if I do,

select

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

from units_table

Throws an error. How should I use Window Function specifically to solve this problem? Thank you in advance!

CodePudding user response：

You need MAX() window function:

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

See the demo.

CodePudding user response：

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

works for me