Join the two tables and get the highest salary

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

I have a two tables with the same table definitions as follows:

VB

COL1    CO2
1   1000.00
2   2000.00
3   3000.00
4   4000.00
5   5000.00

VB

COL1    CO2
1   1500.00
2   2000.00
3   77000.00
4   80000.00
5   51500.00

By using joins, I need highest salary like as follows:

CO2
800000

Posted 31-Mar-15 22:43pm

Member 11366322

Add a Solution

Comments

Thanks7872 1-Apr-15 5:00am

Use google.

King Fisher 1-Apr-15 5:05am

you need UNION not Joins.

Maciej Los 1-Apr-15 5:10am

My virtual 5!

King Fisher 1-Apr-15 5:13am

Thanks master ;)

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Magic Wonder · Answer 1 · 2015-03-31T23:10:00

Solution 1

Hi,

Check this...

SQL

SELECT MAX(CO2) FROM 
(
SELECT CO2 FROM TBL1
UNION
SELECT CO2 FROM TBL2
)X

Hope this will help you.

Cheers

Posted 31-Mar-15 23:10pm

Magic Wonder

Maciej Los · Answer 2 · 2015-03-31T23:10:00

Solution 2

As King Fisher[^] mentioned in comment to the question, you - probably - need to use UNION instead of JOIN.

SQL

SELECT MAX(col2)
FROM (
    SELECT col1, col2 
    FROM Table1
    UNION ALL
    SELECT col1, col2
    FROM Table2
) AS T

Posted 31-Mar-15 23:10pm

Maciej Los

Updated 31-Mar-15 23:11pm

v2

King Fisher · Answer 3 · 2015-03-31T23:13:00

Solution 3

Try like this:

1)

SQL

select max(t.col1 )
from  (
    select  *From table3 union all
    select *From table2
)as t

or
2)

SQL

select top 1 col1
from  (
    select  * From table3 union all
    select * From table2
)as t
order by col1 desc

Posted 31-Mar-15 23:13pm

King Fisher

Updated 31-Mar-15 23:19pm

v3

Comments

Maciej Los 1-Apr-15 5:20am

5! My Lord ;)

Member 11366322 1-Apr-15 5:43am

Hi @King Fisher,
Hope you are doing grate.

Thanks for your solution, but i need solution by using join not using union.
Even i too know the solution by using union.

Thanks and Regards
Bharadwaj

King Fisher 1-Apr-15 5:51am

So ,you know what is join ? what is union ?
why do you want Join?

Maciej Los 1-Apr-15 5:52am

So, you need to be more clear and provide more details about joining operation...