CODEWITHSUNDEEP
sqlsql-servergroup-by
kgh
kgh

Reputation: 89

Get max with group by and other other columns

I have created a sample table in dbfiddle: https://dbfiddle.uk/QNWlduMl

I am trying to get the row with max value of column cnt along with all other columns including Response using group by.

Here's my attempt:

select 
     pid,
     propName,
     Address,
     City,
     Response,
     max(cnt) as response_count
from Prop
group by
     pid,
     propName,
     Address,
     City,
     Response

I don't need to group by Response but need to include it as I need it in the select clause.

Expected output:

pid propName    Address City    Response    response_count

1   p1  addr1   c1  3   30
2   p2  addr2   c2  4   60

Upvotes: 1

Views: 377

Answers (1)

John K.
John K.

Reputation: 525

Use ROW_NUMBER() to flag the row with largest cnt within each partition:

SELECT
    pid,
    PropName,
    Address,
    City,
    Response,
    cnt AS responseCount
FROM
    (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY
                pid,
                propName,
                Address,
                City
            ORDER BY
                cnt DESC
        ) AS cntRowNumber
    FROM
        Prop
    ) PropWithRowNumber
WHERE
    cntRowNumber = 1
;

Then just query that table to filter out everything but the top cnt rows.

Upvotes: 4

Related Questions