-- USING WINDOWS FUNCTION select * from (select *, ROW_NUMBER() Over(partition by dept order by salary desc) as highest_payed from Employee) where highest_payed = 1 --(Just change the (Nth value) number 1 to 2 or 5 in order to find the 2nd or 5th highest payed employee) -- Simple method select dept, max(salary) from Employee group by dept
select salary , department_name from (select salary,department_name, row_number() over(partition by department_name order by salary desc) as rn from employee ) where rn =1
q)what if we need name of highest salary dept wise? ans). select name , sal dept from (select max(sal) as max_sal_dept wise, dept from emp groupby(dept)) inner join emp on sal and dept;
Here is the query to find second highest salary of each department. WITH CTE As ( SELECT DEPT_ID, Salary, RANK() OVER(PARTITION BY DEPT_ID ORDER BY Salary DESC) As Rank FROM Tbl_Dept ) SELECT DEPT_ID, Salary FROM CTE WHERE Rank = 2
if we have 2 seperate tables for employee's department and employee's salary.also the employees' salary as well as their department changes with time(but their time are not related),then how to find the employee's salary according to their department.
select distinct a.emp_depart, b.max(emp_salary) from department as a inner join employee as b on a.emp_id=b.emp_id ; As tables are changes with time so it will give the output present in the table at the execution time of the query.
@@ANUJKUMAR-fu4bk this won't work... Basically He's asking if we have highest salary as let's say 6500 and if 2 people have 6500 salary and then we have to display the names along with dept
How count(*) ,deptno from employee group by deptno ensures that only employees will be counted.. I mean may. E some other field present can too be counted... Like salary here..
Fetching Employee name of each department with max salary.🔥🔥🔥🔥🎇🎇🎆🎆 SELECT DEPARTMENT_ID, FIRST_NAME, Salary FROM emp WHERE (DEPARTMENT_ID,Salary) IN (SELECT DEPARTMENT_ID, MAX(Salary) FROM emp GROUP BY DEPARTMENT_ID)
Is it correct to find ename departmant wise having max salary select ename from emp where in (deptno,sal)=(select max(sal),deptno from emp group by deptno);
Bro I wanted to know that how can we able to write a query to find out the 1st two maximum salaries from each dept....can you please let me know how to solve that.
if u are using sql server - select * from ( select * , ROW_NUMBER() over(partition by deptid order by salary desc) as row from bemployee) L where row = 1 or row =2
-- USING WINDOWS FUNCTION
select * from (select *, ROW_NUMBER() Over(partition by dept order by salary desc) as highest_payed
from Employee)
where highest_payed = 1
--(Just change the (Nth value) number 1 to 2 or 5 in order to find the 2nd or 5th highest payed employee)
-- Simple method
select dept, max(salary) from Employee
group by dept
Thanks! These are good methods.
with cte as (
select *, dense_rank(sal) over(partition by dept_no order by sal desc) as 'rnk'
from emp
)
select ename, sal
from cte
where rnk = 1
select salary , department_name from
(select salary,department_name, row_number() over(partition by department_name order by salary desc) as rn
from employee
) where rn =1
q)what if we need name of highest salary dept wise?
ans). select name , sal dept from (select max(sal) as max_sal_dept wise, dept from emp groupby(dept)) inner join emp on sal and dept;
But the question was to find the employee with highest salary
Can be solved using window function
Excellent content in 4 min😇🔥
Here is the query to find second highest salary of each department.
WITH CTE As (
SELECT DEPT_ID,
Salary,
RANK() OVER(PARTITION BY DEPT_ID ORDER BY Salary DESC) As Rank
FROM
Tbl_Dept
)
SELECT DEPT_ID, Salary
FROM CTE
WHERE Rank = 2
Rank() will give wrong output if there are duplicates. Use Dense_rank() instead
In this only 19 video, where other 31 video, please share with us
Please keep the DDL and Insert Command also
thanks for this excellent content
please add captions
great explanation
Thank uuu
Excellent
U didn't display the name of the employee.. please do that too
This video is not download friendly. You cannot watch it on 2x speed and it also doesn't play in the background.
lol
bro, just put the link of the database in the comment description!!
Pls share remaining videos bro
Select department, max(salary) from emp group by department;
Tq sir...Plese upload more videos on SQL quries
Sure,will do it.plzz do share and subscribe.thank u
Select department max(salary) from table group by department
its wrong. u need to use row_number over partition by, order by desc. and cte
why you want partition in simple output, he is trying to solve in simple way.
Can you tell ???
two ways to find second highest salary?
ua-cam.com/video/-6v7ctxC7yk/v-deo.html
How to find name of all the employees with maximum salary department wise?
@@KJAKo that's wrong it will not work if other department have same salary as that of first highest salary
How to display emp names also for same data.. 🤔
if we have 2 seperate tables for employee's department and employee's salary.also the employees' salary as well as their department changes with time(but their time are not related),then how to find the employee's salary according to their department.
select distinct a.emp_depart, b.max(emp_salary) from department as a inner join employee as b on a.emp_id=b.emp_id ;
As tables are changes with time so it will give the output present in the table at the execution time of the query.
@@Md_Danish433 b.max(emp_salary why max?
What if we have to find the employee's name with the highest salary in each department and there we have multiple employees.
select ename from emp where sal in( select max(sal) from emp group by deptno );
@@ANUJKUMAR-fu4bk this won't work...
Basically He's asking if we have highest salary as let's say 6500 and if 2 people have 6500 salary and then we have to display the names along with dept
@@ANUJKUMAR-fu4bk This is correct ig
How count(*) ,deptno from employee group by deptno ensures that only employees will be counted.. I mean may. E some other field present can too be counted... Like salary here..
Fetching Employee name of each department with max salary.🔥🔥🔥🔥🎇🎇🎆🎆
SELECT DEPARTMENT_ID, FIRST_NAME, Salary FROM emp WHERE (DEPARTMENT_ID,Salary) IN (SELECT DEPARTMENT_ID, MAX(Salary) FROM emp GROUP BY DEPARTMENT_ID)
Thankx bro!
done
if dept id is a primary key in employee table how can we have duplicate dept id's in employee table..
Here in this example its not primary key. But if it was then group by on deptno example wouldn't hold importance.
Is it correct to find ename departmant wise having max salary
select ename from emp where in (deptno,sal)=(select max(sal),deptno from emp group by deptno);
SELECT NAME, SALARY, DEPT_ID
from department
WHERE (SALARY, DEPT_ID) IN (select MAX(SALARY), DEPT_ID
from department
Group by DEPT_ID);
Bro I wanted to know that how can we able to write a query to find out the 1st two maximum salaries from each dept....can you please let me know how to solve that.
if u are using sql server -
select * from (
select * , ROW_NUMBER() over(partition by deptid order by salary desc) as row from bemployee) L
where row = 1 or row =2
DOne
Can you find the department wise nth max salary
Yes , it will be taught in comming session .stay tuned.thank u
SELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (ORDER BY salary DESC) rn FROM Employee e ) WHERE rn = N; /*N is the nth highest salary*/
how to find the nth maximum score
Yes , It will be taught in comming sessions.stay tuned for more.thank u
@@3sh66 okk