user1768029 user1768029 - 1 year ago 74
SQL Question

Retrieve table data based on specific condition in Teradata

I have a table with below data:

emp_id | emp_sal | emp_grp

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

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.

vkp vkp
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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download