Follow Our FB Page << CircleMedia.in >> for Daily Laughter. We Post Funny, Viral, Comedy Videos, Memes, Vines...


how to get the maximum among two tables,for example table 1
(dep1) have (emp_id,emp_name,salary) columns and table 2
(dept2) have (emp_id,emp_name,salary) columns,i want which
employee have the maximum salary among two tables?

Answers were Sorted based on User's Feedback



how to get the maximum among two tables,for example table 1 (dep1) have (emp_id,emp_name,salary) c..

Answer / newbie

SELECT MAX(E.Salary) FROM
(SELECT MAX(Salary) SalaryFROM dep1
UNION
SELECT MAX(Salary) Salary FROM dep2) E

Is This Answer Correct ?    35 Yes 5 No

how to get the maximum among two tables,for example table 1 (dep1) have (emp_id,emp_name,salary) c..

Answer / sumathy

Use Cursors:

declare Cursor_Name cursor scroll
for
select max(salary) as salary from dep1
union
select max(salary) as salart from dep2 order by salary desc
open Cursor_Name
fetch absolute 1 from Cursor_Name
deallocate Cursor_Name

Is This Answer Correct ?    12 Yes 2 No

how to get the maximum among two tables,for example table 1 (dep1) have (emp_id,emp_name,salary) c..

Answer / swati tripathi

SELECT
OUTERTABLE.EMPID,
MAX(OUTERTABLE.SALARY)

FROM

(SELECT EMPID,MAX(SALARY) AS SALARY
FROM DEP1
GROUP BY EMPID
UNION ALL
SELECT EMPID,MAX(SALARY) AS SALARY
FROM DEP2
GROUP BY EMPID) AS OUTERTABLE
GROUP BY OUTERTABLE.EMPID

Is This Answer Correct ?    8 Yes 4 No

how to get the maximum among two tables,for example table 1 (dep1) have (emp_id,emp_name,salary) c..

Answer / veeresh kethari

User CTE:

with Temp as(select max(sal) Sal from Table1
union
select max(sal) sal from Table2)
select top 1 * from Temp order by sal desc

Is This Answer Correct ?    3 Yes 0 No

how to get the maximum among two tables,for example table 1 (dep1) have (emp_id,emp_name,salary) c..

Answer / saurabh agrawal

select top 1 empid, empname, max(salary) from (
Select empid, empname, salary = max(salary) from dep1 group
by empid, empname
union all
Select empid, empname, salary = max(salary) from dep2 group
by empid, empname
)A
group by empid, empname order by max(salary) desc

Is This Answer Correct ?    1 Yes 0 No

how to get the maximum among two tables,for example table 1 (dep1) have (emp_id,emp_name,salary) c..

Answer / praveend

Try this. I have tried it.

Select id, name, Max(salary) as salary from
(Select id, name, salary from emp where salary = (Select
Max(salary) from emp)
Union
Select id, name, salary from emp1 where salary = (Select
Max(salary)from emp1)) as B


where salary = (
Select Max(salary) as salary from
(Select id, name, salary from emp where salary = (Select
Max(salary) from emp)
Union
Select id, name, salary from emp1 where salary = (Select
Max(salary)from emp1)) as C
)

Is This Answer Correct ?    1 Yes 0 No

how to get the maximum among two tables,for example table 1 (dep1) have (emp_id,emp_name,salary) c..

Answer / kaka

create table #temp
(salary numeric(18,0)
)
insert into #temp
select max(salary) as salary from EmpSalary
union
select max(salary)as salary from EmpSalary1
select max(salary) from #temp
drop table #temp

Is This Answer Correct ?    1 Yes 1 No

how to get the maximum among two tables,for example table 1 (dep1) have (emp_id,emp_name,salary) c..

Answer / lince p. thomas

select T.empname,salary from
(
select a.empname as empname ,a.salary as salary from dep1 a
union all
select b.empname as empname,b.salary as salary from dept2 b
)T where T.salary=(select max(T.salary) as salary from
(select max(a.salary) as salary,a.empname as empname
from dep1 a group by a.empname
union all
select max(b.salary)as salary,b.empname as empname from
dept2 b group by b.empname)T
)

Is This Answer Correct ?    0 Yes 0 No

how to get the maximum among two tables,for example table 1 (dep1) have (emp_id,emp_name,salary) c..

Answer / akhilesh

Try this. I have tried it.

Select id, name, Max(salary) as salary from
(Select id, name, salary from emp where salary = (Select
Max(salary) from emp)
Union
Select id, name, salary from emp1 where salary = (Select
Max(salary)from emp1)) as B


where salary = (
Select Max(salary) as salary from
(Select id, name, salary from emp where salary = (Select
Max(salary) from emp)
Union
Select id, name, salary from emp1 where salary = (Select
Max(salary)from emp1)) as C
)

Is This Answer Correct ?    0 Yes 0 No

Post New Answer

More SQL Server Interview Questions

What command would you use to create an index?

0 Answers  


I am learning Testing, so i want to learn SQL also because SQL is important for Testing. I want to know which is best Institute in Ameerpet or SR Nagar or any other place in Hyd? Please help me.

7 Answers  


Differnce between sel server 2000 and 2005

3 Answers  


What is the difference between commit and rollback?

0 Answers  


Do you know what is similarity and difference between truncate and delete in sql?

0 Answers  






The external application that is executed in one of the tasks does not have a log file, but only a screen log. How can I save the data from the screen? : sql server management studio

0 Answers  


can anyone explain me the concept of Serialization in Detail and Clear? plz its urgent i have interview on friday (15th feb)

1 Answers  


How many triggers you can have on a table?

0 Answers  


2) Consider a Table name A which has below records ID --- 5 5 5 5 5 Consider another table B which has below records ID -- 5 5 5 5 5 5 5 5 How many rows will be returned by each of the below queries a) select * from A inner join B on A.id = b.ID b) select * from A left join B on A.id = b.ID c) select * from A right join B on A.id = b.ID

2 Answers   Synechron, TCS,


what is call by value & call by reference ?

2 Answers   Cap Gemini,


What does Master database contains?

0 Answers   Abacus,


What are the joins in sql server? : sql server database administration

0 Answers  


Categories