[RESOLVED] SELECT MAX from 2 different tables

[ivansmo]

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

[Zvoni]

Untested

/* 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

[szlamany]

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...

[Zvoni]

5600108[/URL]]

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

[szlamany]

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.

[Zvoni]

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*

[ivansmo]

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 ?

[Zvoni]

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

[ivansmo]

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

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

  con.Open()
                Dim cmd As New SqlCommand("vozila_max_kilometri", con)
                With cmd
                    .CommandType = CommandType.StoredProcedure
                    .Parameters.AddWithValue("@VIN", Trim(TXT_VIN.Text))
                    .Parameters.Add("@MaxValue", SqlDbType.BigInt).Direction = ParameterDirection.Output
                    .ExecuteScalar()

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

[Zvoni]

This is wrong

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

[ivansmo]

Something isn't right here.

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

[Zvoni]

What is "reg"?

And i just saw something....
adjust above to (untested!)

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

[ivansmo]

Reg is registration plate, thank you for helping me

I'll try it in about one hour. Thanks

[ivansmo]

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.

[Zvoni]

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

[ivansmo]

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

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

[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

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

[ivansmo]

/* 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