question

bfener avatar image
bfener asked

subqueries from same table

Hello, I want to list the dists in cities that has max population. I want to use subqueries. I get the total population of dists in the cities. Query 1 : select city, dist, Sum(population)as pop from Table group by city, dist and then I use another query to list dist object in a city that has maximum population ; Query 2: select city, max(pop) as max_pop from (select city, dist, Sum(population)as pop from Table group by city, dist)T group by city In the query 2 I can not list the dist name only city and max dist population. After listing city name and max dist population how can I make another query to list city, dist and max population). Is it possible to a give an alias to query 2 and use it in another subquery like below ? select K.city, T.dist,K.maxpop from ( select city, max(pop) as max_pop from (select city, dist, Sum(population)as pop from Table group by city, dist)T group by city)K where K.city = T.city and Kmaxpop = T.pop I attach the sample table. Thanks, ![alt text][1] [1]: /storage/temp/711-table.jpg
subquery
table.jpg (49.0 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Dave_Green avatar image
Dave_Green answered
I know you stated that you want to use subqueries, and whilst I don't have a subquery solution for you, it is possible to get your results with a CTE as follows: --Set up table & data USE tempdb DECLARE @t TABLE ( --Normally, I'd use better table names for maintenance! city CHAR(2) ,dist CHAR(2) ,village CHAR(2) ,POPULATION INT ) INSERT @t VALUES ('K1','AB','LL',466), ('K1','AB','LT',759), ('K1','AB','KS',777), ('K1','AB','MB',777), ('K1','AC','RT',12317), ('K1','AC','BG',427), ('K1','AC','AD',420), ('K1','AC','DE',2282), ('K1','AC','GE',274), ('K1','AC','VG',398), ('K2','FD','SD',137), ('K2','FD','HJ',483), ('K2','FA','GR',435), ('K2','FA','WQ',1162), ('K3','FT','FD',661), ('K3','FT','PA',822), ('K3','FC','KZ',626), ('K3','HG','ED',523), ('K3','HG','AP',453), ('K3','HG','MU',361) --Extract required list: ;WITH Qry2 (City,max_pop) AS (select city, max(POPULATION) as max_pop from @t group by city) SELECT T.city,T.dist,qry2.max_pop FROM @t T INNER JOIN Qry2 ON T.City=Qry2.City AND T.POPULATION = qry2.max_pop This does NOT use subqueries; as far as I am aware you can't reference a subquery from the outer query (just the other way round). Can you tell us why you specifically want to use subqueries to answer this question? I've included the code to set up the table, which hopefully will be of use to anyone else who can offer a more suitable solution.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I didn't know that there was no referencing a subquery from outer guery. I won't insist on subqueries anymore. :-) Thanks. It helped too much.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.