CODEWITHSUNDEEP
sql-server
Yasin
Yasin

Reputation: 1232

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

My table structure looks like this:

enter image description here

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.

enter image description here

Upvotes: 0

Views: 169

Answers (5)

Yasin Patel
Yasin Patel

Reputation: 11

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

Upvotes: 1

Aj.na
Aj.na

Reputation: 283

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)

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

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

Upvotes: 3

Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

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

Upvotes: 0

Tiago Oliveira de Freitas
Tiago Oliveira de Freitas

Reputation: 546

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)

Upvotes: 0

Related Questions