Limiting the numbers of rows returned with a COUNT(*) function in a WHERE clause as a subquery
I searched a lot but I couldn´t find an explanation at all for my question. Let´s say I have a database with 100 employees and I want to write a query, that tells me the three employees, which earn the most. The query below works, but I have no idea why.
SELECT Name, salary FROM employees e1 WHERE ( SELECT COUNT(*) FROM employees WHERE salary > e1.salary ) < 3;
As far as I know, a
COUNT(*) function only returns the total number of rows in a table. How can it be, that this query still works and returns me the top three earners. I can´t wrap my head around it and the more I think about it, the more I get confused.
where clause of your query uses a correlated subquery that dynamically counts how many employees have a salary higher than the current employee. The correlation is implemented by condition
salary > e1.salary, where
e1.salary refers to the outer row (the current employee). Only employees whose salary is outscored by 2 or less other employees are returned; others are filtered out. This is you get the top 3.
I find that expressing this with a window function is easier to understand:
select name, salary from ( select name, salary, rank() over(order by salary desc) rn from employee ) t where rn <= 3
Or, if you are running Oracle 12 or higher, you can use the
fetch clause, which makes it straight forward:
select name, salary from employee order by salary fetch first 3 rows only;
SQLite Query: Select, Where, LIMIT, OFFSET, Count, Group By, In the from clause, you can specify one or more table or subquery to It is a virtual column, created in the query for displaying the results SQLite Aggregates are built-in functions defined in SQLite that will COUNT(*) counts the number of the rows in the students table which are 10 rows for 10 students. In SQL Server, you can use the TOP clause to limit the rows returned from a query result set. This clause provides similar functionality to LIMIT in MySQL, and ROWNUM in Oracle, although there are differences in how each of these work. Below are examples of using the TOP clause to limit the result set in SQL Server. Example 1 – Basic Usage
First, here's how the Rank() analytic function will help with problems like this:
WITH rankings as ( SELECT Name, salary, rank() OVER (order by salary desc) as theRank FROM employee ) SELECT Name, Salary, theRank FROM rankings WHERE thRank <= 3;
WITH clause creates a virtual table called 'rankings' and uses the
OVER () analytic function to create an ordered column called theRank. Next the main
SELECT statement simply queries out all the rows from that virtual table which match your criteria
theRank <= 3.
Why does yours work? (without any more headache...) Your where clause includes a COUNT(*) but you are counting from employee where salary is greater than all the other salaries in e1 (which is also employee) Since there is no JOIN clause, this amounts to a Cartesian product between employee and e1, limited only by the > sign on salary.
Without too much frustration, think of this as the upper-half of a matrix where all salaries are both across the top and down the side.) The matrix is filled with True or False depending on whether the row salary is greater than the column salary. When you close off the parenthesis and ask for count(*) < 3 you are asking how many rows have only 2 False values (in other words who are the top TWO earners.)
I believe you should have to use <= 3 to get the top THREE earners. (but look into the Rank() and other Oracle analytic functions instead of writing queries with Cartesian products in them.) Here's a decent reference for them.
Writing Subqueries in SQL, FROM ( <<results from inner query go here>> ) sub WHERE sub.resolution = ' NONE' There are two steps to this process: counting the number of incidents each day not include an alias when you write a subquery in a conditional statement. SELECT COUNT(*) FROM tutorial.crunchbase_acquisitions acquisitions� The where clause of your query uses a correlated subquery that dynamically counts how many employees have a salary higher than the current employee. The correlation is implemented by condition salary > e1.salary, where e1.salary refers to the outer row (the current employee). Only employees whose salary is outscored by 2 or less other employees
Since your table has a very small volume (100 records) you could take a simpler route and order your query by salaries descending, and limit the results to 3. Which will effectively show you the top 3 employees with higher salaries.
select Name, Salary from employees where rownum <= 3 order by Salary desc ;
Subqueries in SELECT Statements, A subquery (or inner SELECT statement) is correlated when the value it produces If a subquery returns no value, the query does not return any rows. Query 5- 25 uses the COUNT function to return a value to the main query. No logical limit exists to the number of subqueries a SELECT statement can have, but the size� The LIMIT clause is an optional part of the SELECT statement. You use the LIMIT clause to constrain the number of rows returned by the query. For example, a SELECT statement may return one million rows. However, if you just need the first 10 rows in the result set, you can add the LIMIT clause to the SELECT statement to retrieve 10 rows.
Performing Advanced Queries Using PROC SQL, To limit the number of rows that PROC SQL displays as output, use the In a PROC SQL query, use the WHERE clause with any valid SAS expression to subset PROC SQL calculates summary functions and outputs results differently , proc sql; title 'Frequent Flyers Who Are Not Employees'; select count(*) as Count from� In MySQL, you can use the LIMIT clause to restrict the number of rows returned by a SELECT query. You provide two parameters: the offset number, and the count (the maximum number of rows to be returned).
ROWNUM Pseudocolumn, You can use ROWNUM to limit the number of rows returned by a query, as in this If you embed the ORDER BY clause in a subquery and place the ROWNUM Please refer to the function ROW_NUMBER for an alternative method of� This query uses the analytic function ROW_NUMBER, which returns a row number for each row ordered by the field specified (in this case, the name field). Method 3 – Fetch. In Oracle 12c, a new method for limiting rows or starting at offsets was introduced. SELECT * FROM yourtable ORDER BY name OFFSET 50 ROWS FETCH NEXT 10 ROWS ONLY;
Using the Group Functions Questions, The COUNT function counts the number of rows; The COUNT(*) function counts the Answer: D. HAVING Clause is used for restricting group results. A sub query that defines a view cannot include the GROUP BY clause; A view is created� The OFFSET clause specifies the number of rows to skip before starting to return rows from the query. The offset_row_count can be a constant, variable, or parameter that is greater or equal to zero. The FETCH clause specifies the number of rows to return after the OFFSET clause has been processed.
- Thanks for the explanation and thank you for the other solution as well. So to finally get this right now:
SELECTstatements always returns me the number of colums in a table. But as soon as I run
COUNT(*)as a subquery within a
WHEREclause, it returns the whole row, back to the main query and then the output depends on what is in my main
SELECTstatement? Is this correct? Thank you for the effort!
< 3is the correct condition in OP’s query, it means: there are maximum two higher salaries, which makes the query generate the top 3 earners (assuming no ties in third position).
limitdoes not exist in Oracle.
- @GMB You're correct. I've made a confusion with MySQL. I have edited my answer to the correct oracle keyword.