SQL Query | How to find Maximum of multiple columns | Values
ฝัง
- เผยแพร่เมื่อ 4 พ.ค. 2021
- 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?
• How to install SQL Ser...
Check out the complete list of SQL Query Interview Questions -
• SQL Query Interview Qu...
Sign up for a free trial of Coupler.io - The No code data integration tool
app.coupler.io/register/sign_...
Get USD 100 off Coursera Plus Annual Subscription
imp.i384100.net/Yg6nxR
Get 50% off Skillshare Annual Subscription with code AFF50.
Dates: 11/24 at midnight - 11/28 at midnight
skillshare.eqcm.net/5b6Z3N
Best Data Science / Analytics / SQL courses
Learn SQL Basics for Data Science Specialization
imp.i384100.net/qnXYk5
Beginners to Python Programming
skillshare.eqcm.net/GjMakm
Data Science and Business Analytics with Python
skillshare.eqcm.net/JrM1Aq
IBM Data Science Professional Certificate
imp.i384100.net/LPQvg3
Data Science Fundamentals with Python and SQL Specialization
imp.i384100.net/mgVYre
Python for Everybody Specialization
imp.i384100.net/DVz7Aj
Google Data Analytics Professional Certificate
imp.i384100.net/OR37oQ
Coursera Plus - Data Science Career Skills
imp.i384100.net/c/3299742/132...
Please do not forget to like, subscribe and share.
For enrolling and enquiries, please contact us at
Website - knowstar.org/
Facebook - / knowstartrainings
Linkedin - www.linkedin.com/company/know...
Email - learn@knowstar.org
![[LIVE] : ONE ลุมพินี 69 | คู่เอก "กุหลาบดำ vs นาบิล"](http://i.ytimg.com/vi/PNM6U8e_HMo/mqdefault.jpg)
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!
imp.i384100.net/Ke51on
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.
neither did I, this actually is a sql standard that sqlserver, db2, postgresql all supports, mariadb supports it since 10.3 and mysql supports it since 8.0.19 with slight different syntax
Thank you so much.
@@LearnatKnowstar hi.. Could you please make a video on performance tuning on 10M+ rows of data.. Also i need a suggestion on comparing two tables columns data with same data with 500+ columns.. I need to know easily which columns data were changed and which columns data were not changed... Could you please suggest me your experience on these points..
Thanks in advance
+1 😅
Great info as always!!!!
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!
Glad I could help!
Great job
Just now, I have fortunately seen this video and subscribed this channel. Awesome explanation 👍
Thank you so much for your support!
This is a GREAT TH-cam Channel! Thank you!
Thank you so much 😊
Thanks for this. If anyone else if looking at this, 4:48sec worked for me
Thank you
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;
In this example, how can i get Max value along with the corresponding Year.
@@subhanivasareddythummapudi3836 you’ll need to add Year column in the select statement.
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
Keep it up..thanks...hopefully more to come...
Thank you
Thank you very much. Great , I enjoyed! I want more such videos.
Glad you liked it!
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
Greatest function will also work I guess to pickup max value for multiple columns.. Correct me if I'm wrong.... Topic is super 👍👍👍
Yes, it should work for certain databases. Thanks for mentioning it 👍
Awesome 👍
Thank you
*GENIUS!!!* 😂😂
How do we select the column as well? Which shows which is having highest sales ?
We can also use pivoting on it
can you please provide the create & insert statement for this. Thanks for such a wonderful content.
Wow awesome knowledge.. Thanks for sharing
Thank you 😊
Great video keep posting more
Thank you
Wow nice superb...
Thank you
Is the queary same for mysql also.?
Can you write for same scenario in oracle sql
How do I get the column name for the max value selected? i am running into issue where i want to select the column header of the max value in a row. any help is appreciated
Very nice - too bad Values is not supported in Azure Synapse - had to use Un pivot to achieve it
How to get the column header name of each row maximum value in a separate column?
Select empID, firstname, last name, (Select Max(Salary) from (Values ([2019]),([2020]), ([2021)) as People(Salary) as MaxSalaryperYear from HR
Can you tell me how the query executes ? I mean executions steps in few lines .
As my understanding 2015, 2016, 2017 , 2018, 2019 , 2020 are in Row and later with inner query it will be in one column and then ....
I think Uttam it will use pivot internally
Rahul I tried to understand this practically by running this case in sql server but unable to understand 100% . I will try later
How can we achieve with case?
Or you do Unpivot and select max
Hi how i can practice these query , i am writing this query in sqllive SELECT * FROM (VALUES (1) ,(2) ,(3)) as TB1(A); grtting error invalid table
You can practice it by installing sql server on your local machine. The below tutorial can walk you through the steps
th-cam.com/video/ncj0EDzy_rw/w-d-xo.html
In this example, how can i get Max value along with the corresponding Year.
Query: names are Ajay Vijay Sunil having 6 subjects in separate columns display the percentage of students who scored 60% in above in overall marks
This could help someone if working with Oracle DB
create table order_tab
(
v_dept varchar2(30),
year_2018 number,
year_2019 number,
year_2020 number,
year_2021 number,
year_2022 number
);
insert into order_tab values('A',1,2,3,4,5);
insert into order_tab values('B',10,20,30,40,50);
insert into order_tab values('C',10,25,30,45,50);
insert into order_tab values('D',15,20,35,40,55);
insert into order_tab values('E',10,20,35,45,55);
--select * from order_tab;
select y.* from(
with demo as ( select 1 as year_2018, 2 as year_2019, 3 as year_2020, 4 as year_2021,5 as year_2022 from dual )
select * from order_tab
unpivot(year_values for year_col in (year_2018, year_2019, year_2020, year_2021,year_2022))
where year_values = (select max(year_values) from order_tab unpivot(year_values for year_col in (year_2018, year_2019, year_2020, year_2021,year_2022)))
)x join order_tab y on(x.v_dept = y.v_dept);
does this work in sqlite?
Might not work
1:10 it starts here
TH-cam is full of tutorial
MYSQL QUERY for this excersis ........................................>
select Name,greatest(First,Second,Third) AS MAXValueOfThreeColumns from MaxOfThreeColumnsDemo;
If you are faced to solve such weird tasks than maybe your data model is not properly developed. Storing rows in columns is not the best way.
Didn't understand 😢
Microsoft at the pick of its laziness... Despite many requests, they didn't implement "Greatest" or "least" functions, despite they're available in many open source sql versions... But, hold a sec, actually they did... But only in Azure version... Shame on you Microsoft! BTW. Did not see the movie, but I'm sure you've presented very user friendly and straightforward approach with cross apply... The second thing, you simply cannot implement this by yourself because MS for years didn't introduce functions with optional parameters or package of functions. Giving only the "default" (not optional, you need to specify) or horribly bad sql_variant (no implicit conversion). Ohh shame on you Microsoft...
Thank you for mentioning it.
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
I too had same thought.
This is a classic case for use unpivot. Wrapping the technical part of the query as a CTE and separating the business logic makes the code more readable.
WITH UnPvtSales AS
(
SELECT Category, Yr, Sale
FROM (
SELECT Category
,[2015]
,[2016]
,[2017]
,[2018]
,[2019]
,[2020]
FROM dbo.Sales) P
UNPIVOT
(Sale FOR Yr IN ([2015],[2016],[2017],[2018],[2019],[2020])
)AS unpvt
)
SELECT Category,
Max (Sale) AS MaxSales
FROM UnPvtSales
GROUP BY Category