SQL: Select MAX value from 2 conditions-CodePudding

I have 1 table and 4 columns. I would like to get max value from visit and spending with use condition as max visit first and max spending second.

The result should be shown on the YELLOW highlight on Image above.

For my code below. Please help me in a better way to do this.

select tt.*
from #max_location as tt,
     (select member_number, 
             max(max_visit) as maxvisit,
             max(spending) as maxspending
      from #max_location
      group by member_number) as max_visit
where tt.member_number = max_visit.member_number 
  and tt.max_visit = max_visit.maxvisit
  and tt.spending = max_visit.maxspending 
order by tt.member_number desc

CodePudding user response：

SELECT      member_number, 
            MAX(max_visit) AS maxvisit,
            MAX(spending) AS maxspending
FROM        #max_location 
GROUP BY    member_number

CodePudding user response：

This would be the ANSI/ISO SQL way of writing the query:

select tt.*
from #max_location tt
join (
       select member_number, 
              max(max_visit) as max_visit,
              max(spending)  as max_spending
       from #max_location
       group by member_number
     ) as mv on mv.member_number = tt.member_number
            and mv.max_visit     = tt.max_visit 
            and mv.max_spending  = tt.spending 
order by tt.member_number desc