group a field to get maximum value

[Parkavi]

i need the year and Data accordingly which has highest Global Sales it should only group by year and give the other data as per the highest global sale of each year in lookml

[andy4]

You need a dimensionalized measure. In your situation, you need to take information which cannot be learned from one row at a time, i.e. which row has the highest value, which requires an aggregate function i.e. a measure. Then you need to use that information to filter out rows in your underlying database tables - something that only actual database columns, i.e. dimensions, can do. Therefore, you need to dimensionalize your measure (great docs article and another i wrote).

In this case, a subquery with a rank or row_number function windowed on year should do the trick. You can do it with sql, but why? NDTs are the best.

view: videogame_sale_rank {
  derived_table: {
    explore_source: videogame_sales {
      column: sale_id {}
      column: year {}
      column: global_sales {}
      derived_column: sale_rank {
        sql: row_number() over (partition by year order by global_sales desc) ;;
      }
      bind_all_filters: yes #not mandatory, but will let your ranking react to any filters on your outer/final query
    }
  }
  dimension: is_top_annual_sale {
    type: yesno
    sql: ${sale_rank} = 1 ;;
  }
}

explore: videogame_sales {
  join: videogame_sale_rank {
    sql_on: ${videogame_sales.sale_id} = ${videogame_sale_rank.sale_id} ;;
  }
}

Now just add that yesno filter to your query and any other dimension you select will only come from the top sale of each year!

Note: I do think I have created a circular dependency here... the bind_all will, I think, pass the yesno filter into the subquery which is impossible to calculate. I have asked Looker Product/eng to add an "Except" to the bind_all_filters logic, or perhaps even better would be to just not pass filters from this view into itself (though with multiple layers of NDTs that might not be enough)... but anyway if that happens you can bind specific filters using a simple bind_filter with identical from_field and to_field. If none of this is making sense, check out the docs on native derived tables! They are, in my opinion, far easier to maintain for other developers compared to blocks of sql.