0

I need to groupby a function and return only rows that have max value for each variable that I choose. In this case the variables are user_id, and task.

My input is shown in the table below:

user_id task    mx
123456  expecd  4.85
789101  Packing 3.17
123456  Packing 1.50
789101  receiv  0.08

And i apply the query below.

select user_id, task, max(time_sum) as mx
from process
group by user_id,task) with data on commit preserve rows;

But it isn’t working to get my target that is the table below.

user_id task    mx
123456  expecd  4.85
789101  Packing 3.17

Could you please guide me to get a solution?

Mr No
  • 113
  • 1
  • 10
Caio Euzébio
  • 182
  • 1
  • 1
  • 10

1 Answers1

3

You want the row with the greatest mx for each user_id.

In Teradata, you can use row_number() and qualify to solve this greatest-n-per-group problem.

select t.*
from mytable t
qualify row_number() over(partition by user_id order by mx desc) = 1
GMB
  • 216,147
  • 25
  • 84
  • 135