Search code examples
sqlteradatagreatest-n-per-groupwindow-functionsalation

Greoupby With Max() condition SQL Teradata

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?

Solution

  • 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