Skip to content
Advertisement

SQL: Select MAX value from 2 conditions

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.

enter image description here

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

Advertisement

Answer

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
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement