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
Answer / newbie
SELECT MAX(E.Salary) FROM
(SELECT MAX(Salary) SalaryFROM dep1
UNION
SELECT MAX(Salary) Salary FROM dep2) E
Is This Answer Correct ? | 36 Yes | 5 No |
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 ? | 13 Yes | 2 No |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
How to get the count of distinct records. Please give me the query?
to explain sql server 2000 architecture & authentication
What is the use of SCOPE_IDENTITY() function?
What is sql collation?
What is nolock hint in sql server 2008
what is diffence between replicaion and logshipping?
How many cores do I need for sql server 2016?
Do you know the isolation level that sql server support?
Hi, I have a table A which has four rows as follows Table A ------- empname salary ------- ------ A 1000 B 2000 C 3000 A 1000 B 2000 D 5000 I need the following output: empname salary ------- ------ A 1000 A 1000 B 2000 B 2000 Thanks in advance
Explain what are partitioned views and distributed partitioned views?
What type of locking occurs during the snapshot generation? : sql server replication
What is replication with database mirroring? : sql server database administration