CODEWITHSUNDEEP
sqlpostgresqlselectsubquerycorrelated-subquery
Newyork111
Newyork111

Reputation: 41

Limiting results in SQL query

I was going trough ways to limit results in SQL and I have come across such a way:

SELECT name
FROM employees e
WHERE 2>=( SELECT COUNT(*) FROM employees e1 WHERE e1.birthdate>e.birthdate);

This query returns three youngest employees but I don't quite get how this query works. How come it is 2>= and not 2<=? Could anyone please shed the light on what this query does? Thank you!

Upvotes: 0

Views: 74

Answers (2)

Deep
Deep

Reputation: 3202

Understand it with example data :

employeeId birthdate
1          12/29/2014
2          11/20/2014
3          01/01/2015
4          11/19/1991

now, evaluate the subquery for each :

employeeId birthdate   subquery result
1          12/29/2014  how many employees have birth date > 12/29/2014 = 1
2          11/20/2014  how many employees have birth date > 11/20/2014 = 2
3          01/01/2015  how many employees have birth date > 01/01/2015 = 0
4          11/19/1991  how many employees have birth date > 11/19/1991 = 3

now, apply the criteria 2>= subquery result. It is certain that it will return empid 1,2,3 not 4.

Note : just for information that there are other approaches to achieve this efficiently.

Upvotes: 1

overflowed
overflowed

Reputation: 1838

SELECT COUNT(*) FROM employees e1 WHERE e1.birthdate>e.birthdate

Gives you the number of employees that are younger than the current employee. So as long as 0, 1 or 2 people are younger than the current employee that row get's returned.

This gives you in the end the three youngest employees. In fact it does only give you the employees with the 3 youngest birth dates (could be more than 3 employees)

Upvotes: 1

Related Questions