# Thread: [RESOLVED] SELECT MAX from 2 different tables

1. ## [RESOLVED] SELECT MAX from 2 different tables

Hy guys and girls.

I need to get the MAX value (MyValue) from two tables. The picture gives a better clue about my problem.

One more thing, table1 contains all the names inside, while table2 doesn't. Another thing, if table2 has the names inside, it is a bigger value than table1 has. That is why I need to get MAX based on these two tables.

Thanks

2. ## Re: SELECT MAX from 2 different tables

Untested
Code:
```/* In table2 there seem to be "duplicates" so we're condensing it down
to one line per name already grabbing the Max-Value existing in table2*/
WITH CTE AS (SELECT Name, Max(MyValue) As MaxValue FROM Table2 GROUP BY Name)

SELECT T1.Name, COALESCE(T2.MaxValue, T1.MyValue) AS MaxValue
FROM table1 As T1
LEFT JOIN CTE As T2
ON T2.Name=T1.Name
WHERE T1.Name='SomeName'```
For 'Spider, above should return 9
For 'John' above should return 12
For 'Bruce' above should return 12
For 'Hero' above should return 13

3. ## Re: SELECT MAX from 2 different tables

Code:
```Select XYZ.Name,Max(XYZ.MaxVal) "MaxVal"
From (Select Name,Max(Value) "MaxVal" From Table1 Group by Name
Union All
Select Name,Max(Value) From Table2 Group by Name) as XYZ
Group by XYZ.Name```
This alternate method should work as well - untested...

4. ## Re: SELECT MAX from 2 different tables

Originally Posted by szlamany;[URL="tel:5600108"
5600108[/URL]]
Code:
```Select XYZ.Name,Max(XYZ.MaxVal) "MaxVal"
From (Select Name,Max(Value) "MaxVal" From Table1 Group by Name
Union All
Select Name,Max(Value) From Table2 Group by Name) as XYZ
Group by XYZ.Name```
This alternate method should work as well - untested...
leave out those two inner Max-Calls. It‘s unnecessary overhead, since you‘re calling Max on the result of the UNION anyway

5. ## Re: SELECT MAX from 2 different tables

Originally Posted by Zvoni
leave out those two inner Max-Calls. It‘s unnecessary overhead, since you‘re calling Max on the result of the UNION anyway
If there is an index on NAME, it might make a difference in how each SELECT performs. I would be curious - but not enough to test this out.

I question the initial table design when a query like this is asked for. Things like the MAX(of "something") should have that "something" be in one "domain" - not two different domains.

6. ## Re: SELECT MAX from 2 different tables

Originally Posted by szlamany;[URL="tel:5600115"
5600115[/URL]]If there is an index on NAME, it might make a difference in how each SELECT performs. I would be curious - but not enough to test this out.

I question the initial table design when a query like this is asked for. Things like the MAX(of "something") should have that "something" be in one "domain" - not two different domains.
I know what you mean, but op‘s scenario is something i have to deal with daily, too in the company i work for (doing data analysis on the DB2 database of our ERP). It‘s the reason for my CTE/Left Join approach

and correct with index comment, but since we don’t know *shrug*

7. ## Re: SELECT MAX from 2 different tables

Originally Posted by szlamany
If there is an index on NAME, it might make a difference in how each SELECT performs. I would be curious - but not enough to test this out.

I question the initial table design when a query like this is asked for. Things like the MAX(of "something") should have that "something" be in one "domain" - not two different domains.
I understand my logic could be wrong, but I'll try to explain what am I trying here.

Table1 is where all the company vehicles are stored. There I have VIN, brand, mileage...
Table2 is a table of each vehicle service. There could be each vehicle from Table1, but not necessary. Example, when a new (used vehicle) vehicle is registered in Table1, and after some time it comes to service, Table2 doesn't have mileage. In my application, I want that mileage to be calculated automatically, (how many miles there are in between each service), and that is why I need to know the last mileage of the vehicle that is currently imported through the app.

2 Zvoni, your query works. I've tested it, and tomorrow I'll make a stored procedure with the output parameter. Thanks

2 szlamany, thank you also. I appreciate any help.

Edit: now I'm thinking, this could be done with something like SELECT MAX(mileage) from Table2, IF NOT EXIST THEN SELECT Table1.mileage ?

8. ## Re: SELECT MAX from 2 different tables

That‘s what COALESCE is doing.
it returns the first NON-Null result

9. ## Re: SELECT MAX from 2 different tables

I'm in trouble of getting value.

Take a look, please. SQL executes and finds the row, but for some reason, I can't make it to pass the @MaxValue to my app.

This is the procedure

Code:
```SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[vozila_max_kilometri]

@VIN varchar(25),
@MaxValue bigint output

AS

BEGIN

WITH CTE AS (SELECT VIN, Max(kilometri) As MaxValue FROM vozila_servisi GROUP BY VIN)
SELECT vozila.VIN, COALESCE(vozila.kilometri, vozila_servisi.kilometri) AS MaxValue
FROM vozila
LEFT JOIN vozila_servisi
ON vozila_servisi.VIN=vozila.VIN
WHERE vozila.VIN=@VIN

/** RETURN; **/

END```
And VB code

Code:
```  con.Open()
Dim cmd As New SqlCommand("vozila_max_kilometri", con)
With cmd
.CommandType = CommandType.StoredProcedure
.ExecuteScalar()

TXT_p_kilometri.Text = cmd.Parameters("@MaxValue").Value.ToString
End With
con.Close()```

10. ## Re: SELECT MAX from 2 different tables

This is wrong
Code:
```WITH CTE AS (SELECT VIN, Max(kilometri) As MaxValue FROM vozila_servisi GROUP BY VIN)
SELECT vozila.VIN, COALESCE(vozila.kilometri, vozila_servisi.kilometri) AS MaxValue
FROM vozila
LEFT JOIN vozila_servisi
ON vozila_servisi.VIN=vozila.VIN
WHERE vozila.VIN=@VIN```
correct (i think)
WITH CTE AS (SELECT VIN, Max(kilometri) As MaxValue FROM vozila_servisi GROUP BY VIN)
SELECT vozila.VIN, COALESCE(CTE.MaxValue, vozila.kilometri) AS MaxValue
FROM vozila
LEFT JOIN CTE
ON CTE.VIN=vozila.VIN
WHERE vozila.VIN=@VIN

11. ## Re: SELECT MAX from 2 different tables

Something isn't right here.

Any chance to join VIN,reg,kilometri from these two tables, and then find MAX value WHERE VIN=''

12. ## Re: SELECT MAX from 2 different tables

What is "reg"?

And i just saw something....
WITH CTE AS (SELECT VIN, Max(kilometri) As MaxValue FROM vozila_servisi GROUP BY VIN)
SELECT vozila.VIN, @MaxValue=COALESCE(CTE.MaxValue, vozila.kilometri)
FROM vozila
LEFT JOIN CTE
ON CTE.VIN=vozila.VIN
WHERE vozila.VIN=@VIN

13. ## Re: SELECT MAX from 2 different tables

Reg is registration plate, thank you for helping me

I'll try it in about one hour. Thanks

14. ## Re: SELECT MAX from 2 different tables

Code:
```WITH CTE AS (SELECT VIN, Max(kilometri) As MaxValue FROM vozila_servisi GROUP BY VIN)
SELECT @MaxValue=COALESCE(vozila.kilometri, vozila_servisi.kilometri)
FROM vozila
LEFT JOIN vozila_servisi
ON vozila_servisi.VIN=vozila.VIN
WHERE vozila.VIN=@VIN```
It seems this one is working. Tonight I'll do more tests but at first look, it seems that query works.

15. ## Re: SELECT MAX from 2 different tables

Originally Posted by ivansmo
Code:
```WITH CTE AS (SELECT VIN, Max(kilometri) As MaxValue FROM vozila_servisi GROUP BY VIN)
SELECT @MaxValue=COALESCE(vozila.kilometri, vozila_servisi.kilometri)
FROM vozila
LEFT JOIN vozila_servisi
ON vozila_servisi.VIN=vozila.VIN
WHERE vozila.VIN=@VIN```
It seems this one is working. Tonight I'll do more tests but at first look, it seems that query works.
WRONG!
Look closely at my colored comments!
In the "main" SELECT-Statement, i don't access table "vozila_servisi" BUT the CTE

16. ## Re: SELECT MAX from 2 different tables

Actually the query I've edited doesn't work.

When I try to ALTER your original code, I get this warning

17. ## Re: [RESOLVED] SELECT MAX from 2 different tables

/* Put my statement without a WHERE clause into a view, then in your SP it‘s a simple select of MaxValue from the view with your WHERE clause */

alternative is szlamany‘s UNION approach

EDIT: Just found the explanation: https://stackoverflow.com/questions/...riable-must-no
The Problem is not the CTE or COALESCE: It's the "additional" Field of VIN in the SELECT
Remove the VIN from the SELECT
WITH CTE AS (SELECT VIN, Max(kilometri) As MaxValue FROM vozila_servisi GROUP BY VIN)
SELECT @MaxValue=COALESCE(CTE.MaxValue, vozila.kilometri)
FROM vozila
LEFT JOIN CTE
ON CTE.VIN=vozila.VIN
WHERE vozila.VIN=@VIN
"vozila.VIN," removed

18. ## Re: [RESOLVED] SELECT MAX from 2 different tables

Originally Posted by Zvoni
/* Put my statement without a WHERE clause into a view, then in your SP it‘s a simple select of MaxValue from the view with your WHERE clause */

alternative is szlamany‘s UNION approach

EDIT: Just found the explanation: https://stackoverflow.com/questions/...riable-must-no
The Problem is not the CTE or COALESCE: It's the "additional" Field of VIN in the SELECT
Remove the VIN from the SELECT

"vozila.VIN," removed
Yap, yesterday I removed vozila.VIN and today I tested query carefully. It is exactly what I want it to do

Thank you very much

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•