Returning top value with associated columns

August 9, 2024

I am trying to get the top row for each city. I want to know the key and the count associated with the top row.

Assume the following data

Key City        City_count
13  Columbus    30
4   Columbus    17
16  Grove City  15  
29  Columbus    27

I need the SQL to return, for each CITY, give me the top City_count, and the Key for that City_count value. In the above data set, the output should return

Key City        City_count
13  Columbus    30
16  Grove City  15

I think I need to use
(dense_rank first order by city, city_count desc) over (partition by city), but I can’t get it to return what I need. What am I doing wrong?

>Solution :

You can use row_number with a partition on City to determine the row with the max value, then select it:

select t.Key
    , t.City
    , t.City_Count
from
(
    select tbl.*
        , row_number() over (partition by tbl.City order by tbl.City_Count desc) rn
    from tbl 
) t
where t.rn = 1