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 |
how to improve the query perfoemance in teradata.with example?and how explain this in interview?please forward answer
one table have input no name 10 rao 20 siva 30 srinu 10 rao i want to ouput like this way no name 20 siva 30 srinu 10 rao how it posible in only sql server query?not oracle?
suppose one Cylinder split data blocks are moved from where?
IBM QUESTION As all we know PE takes the SQL Request and it will give responce for that query. what is the internal process happening.how it is taking that sql from the system????? thanks in Advance
2 Answers Cap Gemini, IBM, Infosys,
What are the frequently used data types in teradata?
what is the difference between start schma and Fsldm?
What is smp and mpp platforms?
How many macros we can create inside a macro
What is bteq utility in teradata?
If the script is aborted. why it is aborted and how can you identify ?
Explain the term 'columns' related to relational database management system?
can we join volatile table with general table an global temporary table with general table ?