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.
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
Does SDLC changes when you use Teradata instead of Oracle?
My table got locked during mload due to a failed job. What do I do to perform other operations on it?
Aborted in Phase 1 data Acquistion Incomplete in fastload?
Highlight a few of the advantages that ETL tools have over Teradata.
Can any one please provide me practical example of How to do the performance tuning in Query??
Explain teradata utilities. What is multiload, fast load, tpump?
Why teradata is used?
There is a column with date in it. If I want to get just month how it can be done? Can I use sub string?
What do you mean by parsing?
What is meant by a Least Cost Plan?
How will you solve the problem that occurs during update?
Give some points about Teradata Viewpoint ?