April 9, 2003 at 9:35 pm
Hi All,
I need a query to find Max() of a particular field which is in more than one table. for example i have 3 different table, account, sales, marketing. i want to find who is getting maximum salary from all these department. Thank you
Ravi
April 10, 2003 at 2:19 am
Try using a derived table e.g.
select max(salary) from
(select max(sal) salary from account
union select max(sal) from sales
union select max(sal) from marketing) as allsalaries
April 10, 2003 at 4:38 am
There might be other ways but right off you are looking for something like this
Select emp, sal from
( -- List all employees and their salaries
select emp, sal from account
union
select emp, sal from sales
union
select emp, sal from marketing
) as allempsals
where sal = ( -- get the highest amount made from all tables
select max(salary) from
(
select max(sal) as salary from account
union
select max(sal) from sales
union
select max(sal) from marketing
) as allsalaries
)
April 14, 2003 at 4:43 am
Thank you very much
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply