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

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

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 by Tiago Oliveira de Freitas for how to write sql query for getting primary key in a query where aggregate functions are used

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 by Giorgi Nakeuri for how to

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

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 by Nguy?n H?i Tri?u for how to write sql query for getting primary key in a query where aggregate functions are used

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 by Ajaynaidu Pappala for how to write sql query for getting primary key in a query where aggregate functions are used

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)   

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

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  

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72