I have a employee table with EMPID, EMPNAME, DEPTID, SAL and want to fetch the

I have a employee table with EMPID, EMPNAME, DEPTID, SAL
and want to fetch the maximum and minimum salary on each
dept id with the emp name. Can anyone help in this? The
result should contain the EMPNAME, DEPTID, SAL.

Question Posted / hariharan

4 Answers
12357 Views
Cap Gemini, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

I have a employee table with EMPID, EMPNAME, DEPTID, SAL and want to fetch the maximum and minimum..

Answer / gayathri

Sel Empname,deptid, (qualify row_number() over (partition by deptid order by sal asec)=1) as min_sal, (qualify row_number() over (partition by deptid order by sal desc)=1) as max_sal from EMP

Is This Answer Correct ?

5 Yes

0 No

I have a employee table with EMPID, EMPNAME, DEPTID, SAL and want to fetch the maximum and minimum..

Answer / ragunath

sel empname,deptid,sal
from employee where (deptid,sal) in
(sel deptid, max(sal)from employee group by 1
)
union
(sel empname,deptid,sal
from employee where (deptid,sal) in
(sel deptid, min(sal) from employee group by 1
));

Is This Answer Correct ?

1 Yes

1 No

I have a employee table with EMPID, EMPNAME, DEPTID, SAL and want to fetch the maximum and minimum..

Answer / hariharan

sel empname, a.deptid, b.salary
from t1_metadata.emp_test a,
(
sel
deptid, max(sal)
from t1_metadata.emp_test
group by 1
union
sel
deptid, min(sal)
from t1_metadata.emp_test
group by 1
)b (deptid, salary)
where a.deptid = b.deptid
and a.sal=b.salary

Is This Answer Correct ?

8 Yes

11 No

I have a employee table with EMPID, EMPNAME, DEPTID, SAL and want to fetch the maximum and minimum..

Answer / syamal rao

You can try the bellow query in BTEQ of TERADATA.

select empname from employee with max(sal),min(sal) by deptid;

Is This Answer Correct ?

1 Yes

8 No

Post New Answer