how to write sql query for getting primary key in a query where aggregate functions are used

Question

My table structure looks like this:

I want id(primary key) of city with max population statewise.

If there is a tie in max population in particular state then any one id of those rows should be selected.

Answer 1

Use window function for this:

with cte as(select *, row_number() 
                      over(partition by state order by population desc, id) rn from table)
select * from cte where rn = 1

If there can be several rows with max population then you can try rank function instead of row_number:

with cte as(select *, rank() 
                      over(partition by state order by population desc) rn from table)
select * from cte where rn = 1

Answer 2

required sql query:

SELECT *
FROM(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY state ORDER BY population DESC) AS RowNum
    FROM dbo.tbl_city
    ) s
WHERE s.RowNum = 1

Answer 3

Try this:

select id, state, population
from tbl_city a
where id = (select top 1 id from tbl_city where state = a.state order by population DESC)

Answer 4

You can try this code:

SELECT * FROM (
   SELECT id, state, population,
      ROW_NUMBER() OVER(PARTITION BY state ORDER BY population DESC) AS rn
   FROM tbl_city) AS A
WHERE rn = 1

Answer 5

Try this,

SELECT id,
       state,
       population
FROM   #yourtable A
WHERE  population IN(SELECT Max(population) AS population
                     FROM   #yourtable A1
                     WHERE  A.state = A1.state
                     GROUP  BY state)