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
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
@@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
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
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
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;
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
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
You can practice it by installing sql server on your local machine. The below tutorial can walk you through the steps ruclips.net/video/ncj0EDzy_rw/видео.html
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
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 ....
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
MYSQL QUERY for this excersis ........................................> select Name,greatest(First,Second,Third) AS MAXValueOfThreeColumns from MaxOfThreeColumnsDemo;
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);
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...
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
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 😅
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!
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
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 👍
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
can you please provide the create & insert statement for this. Thanks for such a wonderful content.
Thank you very much. Great , I enjoyed! I want more such videos.
Glad you liked it!
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.
This is a GREAT RUclips Channel! Thank you!
Thank you so much 😊
Is the queary same for mysql also.?
Thanks for this. If anyone else if looking at this, 4:48sec worked for me
Thank you
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
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
How do we select the column as well? Which shows which is having highest sales ?
Keep it up..thanks...hopefully more to come...
Thank you
We can also use pivoting on it
Just now, I have fortunately seen this video and subscribed this channel. Awesome explanation 👍
Thank you so much for your support!
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
ruclips.net/video/ncj0EDzy_rw/видео.html
Can you write for same scenario in oracle sql
How to get the column header name of each row maximum value in a separate column?
Great job
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.
Very nice - too bad Values is not supported in Azure Synapse - had to use Un pivot to achieve it
Great info as always!!!!
Wow awesome knowledge.. Thanks for sharing
Thank you 😊
does this work in sqlite?
Might not work
How can we achieve with case?
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
Great video keep posting more
Thank you
Awesome 👍
Thank you
Wow nice superb...
Thank you
*GENIUS!!!* 😂😂
In this example, how can i get Max value along with the corresponding Year.
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
Select empID, firstname, last name, (Select Max(Salary) from (Values ([2019]),([2020]), ([2021)) as People(Salary) as MaxSalaryperYear from HR
Or you do Unpivot and select max
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
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.
MYSQL QUERY for this excersis ........................................>
select Name,greatest(First,Second,Third) AS MAXValueOfThreeColumns from MaxOfThreeColumnsDemo;
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);
1:10 it starts here
select GREATEST(1,5,18)
select LEAST(1,5,18)
RUclips is full of tutorial
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.
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