- Subquery Basics
- Subqueries in the FROM Clause
- Subqueries in the SELECT Clause
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Subqueries in the FROM Clause
Let's look at a similar query using different data. To examine the number of sales orders each store has produced, you write a query like this:
select stor_id, count(sonum) as orders_by_store from sales group by stor_id stor_id orders_by_store ------- --------------- 6380 2 7066 2 7067 2 7131 3 7896 3 8042 4 (6 row(s) affected)
To see just the highest value, you might consider adding a HAVING clause with a nested aggregate:
select stor_id, count(sonum) as orders_by_store from sales group by stor_id having count (sonum) = max (count (sonum))
However, in most systems, you'll get an error message or no rows: nested aggregates are prohibited. Some versions of Transact-SQL allow nested aggregates in the HAVING clause, and return the results you expect.
Adaptive Server Enterprise
stor_id orders_by_store ------- --------------- 8042 4 (1 row affected)
What do you do if your SQL dialect does not support aggregates inside aggregates? Is there a way to find the store with the largest number of orders? Try a FROM subquery. It creates a "virtual view."
Create a query that returns one column, the value of orders for each store.
select count(sonum) as orders_by_store from sales group by stor_id orders_by_store --------------- 2 2 2 3 3 4 (6 row(s) affected)
Convert the query to a FROM clause subquery—a kind of on-the-fly view from which the outer query will pull data. Assign a table alias (here FROMsubq) to the subquery in the FROM clause.
select FROMsubq.orders_by_store from (select count(sonum) as orders_by_store from sales group by stor_id) FROMsubq orders_by_store --------------- 2 2 2 3 3 4 (6 row(s) affected)Use the subquery "column" as an argument to an aggregate in the outer query.
select max(FROMsubq.orders_by_store) from (select count(sonum) as orders_by_store from sales group by stor_id) FROMsubq ----------- 4 (1 row(s) affected)To include store numbers in the results, create a new query with grouping by stores. Then move the original query (with its FROM subquery) to the outer query's HAVING clause.
select stor_id, count(sonum) as max_orders from sales group by stor_id having count(sonum) = ( select max (FROMsubq.orders_by_store) from ( select count(sonum) as orders_by_store from sales group by stor_id ) FROMsubq ) stor_id max_orders ------- ----------- 8042 4 (1 row(s) affected)