I've seen queries that use max(value) with group by to select highest value where duplicate ID (SQL Select highest value where duplicate ID), however, I believe I'm not applying the approach correctly and was hoping someone could assist.

I need to do a select into statement, as I'll be referencing this table at a later point. Keeping that in mind, my code looks like this:

select class, record_id, max(salesprice) as salesprice, category, zone
into ##weeklysalestable
from ##salestable
where class in ('residential')
group by class, record id, category, zone
order by  record_id, class, category,zone

The problem is that SQL is prompting me to name column 3 (so i added that to the code above) which then I believe is causing the output to not drop the duplicate with the lower salesprice. Goal is simply to keep the the record_id with the highest sales price.

Can max() not be applied in this scenario? If so, is there an alternative approach?

Sample source data:

Desired Output:

Any advice would be great.

CodePudding user response：

One option is using the window function row_number() over() in a subquery

select class,
       record_id,
       salesprice,
       category, 
       zone
into ##weeklysalestable
from ( Select *
             ,rn = row_number() over (partition by class,category,zone order by salesprice desc)    -- assuming `record_id` doesn't need to be in the `partition`
        From ##salestable
        where class in ('residential')
     ) A
Where RN = 1

Another option is using WITH TIES (a nudge less performant)

select top 1 with ties 
       class,
       record_id,
       salesprice,
       category, 
       zone
into ##weeklysalestable
from ##salestable
where class in ('residential')
order by row_number() over (partition by class,category,zone order by salesprice desc)  -- assuming `record_id` doesn't need to be in the `partition`