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
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.
12 People are following this question.
