SQL Query | How to find Maximum of multiple columns | Values

In this video, we discuss how to find the maximum value out of multiple columns. We use the Values table constructor in this example.

How to install SQL Server for practice?

Check out the complete list of SQL Query Interview Questions -

Get USD 100 off Coursera Plus Annual Subscription

Get 50% off Skillshare Annual Subscription with code AFF50.
Dates: 11/24 at midnight - 11/28 at midnight

Best Data Science / Analytics / SQL courses
Learn SQL Basics for Data Science Specialization

Beginners to Python Programming

Data Science and Business Analytics with Python

IBM Data Science Professional Certificate

Data Science Fundamentals with Python and SQL Specialization

Python for Everybody Specialization

Google Data Analytics Professional Certificate

Coursera Plus - Data Science Career Skills

Please do not forget to like, subscribe and share.

For enrolling and enquiries, please contact us at

[LearnatKnowstar]

Register at the below link to get US $100 off for Coursera Plus membership between Sep 8 - Sep 29, 2022.
The Coursera Plus membership gets you access to unlimited courses and unlimited certifications!

[xerosdestroyer]

I've been doing SQL professionally for more than 10 years and didn't realize that was a valid syntax. Thanks for the great video.

[landchennai]

declare @Table table ( Id int, a int, b int, c int)
insert into @Table values (1, 10, 20, 30), (2, 10, 20, 30)

select *, case when a > b and a > c then a
when b > a and b > c then b
when c > a and c > b then c end MaxValue
, case when a < b and a < c then a
when b < a and b < c then b
when c < a and c < b then c end SmallValue
from @Table

[JulieWork]

Excellent! You're a lifesaver and thanks for explaining the codes. I saw these codes online but just couldn't wrap my head around it. Thanks again!

[HuyVo-uzwj]

Thanks for your video.
From Oracle SQL we can using bellow syntax:
select Category, GREATEST(nvl(2015, 0), nvl(2016, 0), nvl(2017, 0), nvl(2018, 0), nvl(2019, 0), nvl(2020, 0)) as MAX_SALE
from sales;

[jagadesh]

Greatest function will also work I guess to pickup max value for multiple columns.. Correct me if I'm wrong.... Topic is super 👍👍👍

[samcaseybolade]

Thanks for this. If anyone else if looking at this, 4:48sec worked for me

[Helena-fprt]

Thank you very much. Great, I enjoyed! I want more such videos.

[Reellron]

If you're using SQL Server, you could use unpivot instead. It's more pleasant to type and probably more performant for larger data tables. Taking a similar approach in other SQL programs isn't pleasant to type, but I'd imagine the performance scaling is the same.

SELECT Category, MAX(unpvt.Sales) as MaxSales
FROM dbo.Sales
UNPIVOT (
Sales FOR Years IN ([2016], [2017] [2018], [2019], [2020])
) unpvt
GROUP BY Category

[chandanpatra]

can you please provide the create & insert statement for this. Thanks for such a wonderful content.

[aal]

This is a GREAT YouTube Channel! Thank you!

[SureshKumar-lpsb]

Just now, I have fortunately seen this video and subscribed this channel. Awesome explanation 👍

[landchennai]

declare @Table table ( Category varchar(10), a int, b int, c int)
insert into @Table values ('A', 10, 20, 30), ('B', 60, 70, 80)


select Category
, min(Value) as SmallValue
, max(Value) as MaxValue
from
(
Select *
from @Table A
unpivot( value for xyz in ([a], [b], [c])) P
) v
group by Category

[ouramazingnature]

Hi there, awesome video, but I feel that this method is going to be difficult to apply for databases with high column number count because we have to write every column name inside the value statment. Do you have another way to do the same thing?. Thanks

[sudhakarv]

Can you please do video on how get different highest salaries for different departments like for Department A need 2nd highest salary and for Department B need 5th highest Salary

[sanc]

Keep it up..thanks...hopefully more to come...

[bagusikhsanbagaskara]

Wow awesome knowledge.. Thanks for sharing

[landchennai]

select GREATEST(1, 5, 18)

select LEAST(1, 5, 18)

[TheChatGPTEngineer]

How do we select the column as well? Which shows which is having highest sales ?

[avirozenboim]

Very nice - too bad Values is not supported in Azure Synapse - had to use Un pivot to achieve it