user1768029 - 1 year ago 51

SQL Question

I have a table with below data:

`emp_id | emp_sal | emp_grp`

1 5 HMCCR

1 10 HMCPR

1 20 HMCPR

1 30 HMCPR

1 40 HMCRR

2 40 HMCRR

2 50 HMCCR

I need to write sql in Teradata where i need to find min(emp_sal) for each group of emp_id.If one or more rows of emp_id has emp_grp='HMCPR' then retain only those rows and take min of emp_sal. Do not do anything when none of the rows have emp_grp='HMCPR' and take min of emp_sal from that group.

Apologize, if you are confused. Based on above condition my output should look like this:

`emp_id | emp_sal | emp_grp`

1 10 HMCPR

2 40 HMCRR

I tried below query but it gives min(emp_sal) for each group as i used group by emp_id, emp_grp

`sel`

emp_id,

case when emp_grp='HMCPR' then min(emp_sal)

else min(emp_sal) end emp_sal, emp_grp

from db_wrk.emp_sin

group by emp_id, emp_grp

Can anyone help me to get expected result in teradata.

Answer Source

You can use `row_number`

with some logic to get the emp_grp HMCPR to be ordered first if it exists.

```
select emp_id,emp_sal,emp_grp
from (
select e.*,
row_number() over(partition by emp_id
order by case when emp_grp = 'HMCPR' then 0 else 1 end,emp_sal) as rn
from db_wrk.emp_sin e
) t
where rn = 1
```