Hey there! 👋 For more interesting content, tutorials, and updates, Feel free to connect with me on Instagram Handles :- @createwithchirag - instagram.com/createwithchirag/ @learn.with.chirag - instagram.com/learn.with.chirag/ LinkedIn: www.linkedin.com/in/chirag-sehgal-9200111b8/ Let's stay connected and keep the creativity flowing! 💡
@mcspraint45913 ай бұрын
--We can also use rank in such cases WITH salary_ranks AS ( SELECT e.id, d.id, d.name, e.name as employee_name, e.salary, DENSE_RANK() OVER ( PARTITION BY e.departmentid ORDER BY e.salary DESC ) AS sal_rank FROM Employee AS e LEFT JOIN Department AS d ON d.id = e.departmentid ) SELECT name as Department, employee_name as Employee, salary as Salary FROM salary_ranks WHERE sal_rank < 4
@ritikamehta2767Ай бұрын
Great solution ! how can i think like this and make such logics ?
@sarveshkulkarni62872 ай бұрын
samaj nahi aa raha ye wala query
@sayanchakraborty21947 ай бұрын
I don't understand that why 3 > subquery, can u please explain this. thanks in advance
@learnwithchirag6 ай бұрын
3 > subquery is evaluating whether the value 3 is greater than the result of the subquery. The sub query counts the number of distinct salaries (COUNT(DISTINCT (e2.Salary))) from the Employee table where the salary is greater than the salary of the employee in the main query (e1.Salary) and where the department IDs match (e1.DepartmentId = e2.DepartmentId). So, 3 > subquery is essentially checking if there are fewer than 3 distinct salaries in the Employee table that are greater than the salary of the current employee (e1.Salary) in the main query and belong to the same department (e1.DepartmentId = e2.DepartmentId). If the count of distinct salaries that meet the specified conditions in the subquery is less than 3, then the condition 3 > subquery is true, and the employee is included in the result set. Otherwise, if the count is 3 or more, the condition is false, and the employee is excluded from the result set.
@redeagle65806 ай бұрын
but how joe and henry with same got included and count goes to the 4 and i didn't understand how distinct will give same values in case of joe and henry@@learnwithchirag
@kashifraza57638 ай бұрын
Hello @Chirag, Can you explain more about the concept of E2 Employee in subqueries?Can you gave some brief about this concept or link where i can read. Thanks in advance
@learnwithchirag7 ай бұрын
See , When within the same table we have to check if one salary is greater than the other , or basically if we have to compare two rows in a single table we create Aliases , here I created e1 and e2 in order to compare between two different rows of a single table. Now do watch my explanation part of WHERE , this will make more sense to you.
@vineetkarmakar230524 күн бұрын
subquery to bohot hi bekar hai iss question ke lie we can use windows function
@redeagle65806 ай бұрын
can you do this with window function?
@sandeepvarma896 ай бұрын
Bhai mene try Kiya par windows function se IT valo ka ek partition ho jayega aur sales valo ka alag voh vaise as per question ans proper aayega top 3 salaries par submit karne par accept nahi hoga kyunki sequence match nahi hoga