Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

add highes as a column using Window Functions

I have a table like below:

Pic1

Query to replicate data:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

 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:

P2

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!

>Solution :

You need MAX() window function:

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

See the demo.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading