Max() Query

  • 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

  • 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

  • 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

    )

  • 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