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! 💡
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
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.
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
--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
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
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.
![MLV Prasad - LeetCode SQL [ MEDIUM ] | | "1321 " | Restaurant Growth](/img/1.gif)
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! 💡
Great solution ! how can i think like this and make such logics ?
can you do this with window function?
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
I don't understand that why 3 > subquery, can u please explain this. thanks in advance
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.
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
--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
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
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.
samaj nahi aa raha ye wala query
subquery to bohot hi bekar hai iss question ke lie we can use windows function