Join on max value in SQL

Question

I have been trying to join based on max column values of a table Here is my table structure

Table A:

| ID | Val | text |
| 1  | 423 | tuh  |
| 2  | 369 | foo  |
| 3  | 568 | bars |
| 4  | 789 | ther |

Table B:

| Val | Label |
| 423 | Adria |
| 369 | Adria |
| 568 | Adria |
| 789 | Bang  |

Table C:

| Label | Val |
| Adria | 100 |
| Fox   | 123 |
| Bang  | 789 |

I have tried this query and for some reason I still do not get the result based on max val in table A

select C.Label, A.txt
from C join
     B
     on B.Label = C.Label left join
     A
     on A.Val = B.Val and
     A.Val = (select max(A.Val) from A as a where a.Val = A.val)
where C.Lable = 'Adria'

Output:

Adria tuh
Adria foo
Adria bars

Expected Output:

Adria bars

Could someone please help point out what am I missing?

Your code as written should do what you want. I suspect you have not transcribed correctly your query. — Gordon Linoff, Sep 20 '21 at 11:23
@GordonLinoff: Nope, it is not working. I am still getting the wrong output. I have put the query correctly. — KnowNothing, Sep 20 '21 at 11:25

markp-fuso · Answer 1 · 2021-09-21T15:21:20.433

UPDATE complete rewrite after question updated with more data (see edit history if really interested in my prior ramblings).

Assumptions/Understandings:

A.Val is unique (eg, there is only one row where A.Val = 423) otherwise we'll need more info for the join from B to A (eg, if 3x rows have A.Val = 423 do we return 3 rows/A.text values, or do we limit the match to a single row via additional criteria?)
if no match in table A then return A.text = NULL (hence the left join)

Where I need more input from OP is on the B->A join. From OP's comment we know that for Label = 'Adria' we want to pull the row from A where Val = max(Val) = 568, but what I'm not sure about is if I can apply the max() to B.Val or do I need to apply the max() to A.Val:

limit the B->A join to A.Val = max(B.Val) or ...
limit the B->A join to A.Val = max(A.Val)

One idea for limit the B->A join to A.Val = max(B.Val)

select  CB.Label, A.text
from    (select C.Label, max(B.Val) as maxVal
         from    C
         join    B
         on      C.Label = B.Label
         and     C.Label = 'Adria'
         group by C.Label) as CB
left
join    A
on      A.Val = CB.maxVal
go

 Label      text
 ---------- ----------
 Adria      bars

If we delete A where Val = 568 (so that A.Val = max(B.Val) = 568 fails) the query returns:

 Label      text
 ---------- ----------
 Adria      NULL

One idea for limit the B->A join to A.Val = max(A.Val)

select  dt.Label, A.text
from    (select C.Label, max(A.Val) as maxVal
         from   C
         join   B
         on     C.Label = B.Label
         and    C.Label = 'Adria'
         left
         join   A
         on     A.Val = B.Val
         group by C.Label) as dt
left
join    A
on      A.Val = dt.maxVal
go

 Label      text
 ---------- ----------
 Adria      bars

If we delete A where Val = 568 (so that A.Val = dt.maxVal = 423) the query returns:

 Label      text
 ---------- ----------
 Adria      tuh

If we delete A (so that A.Val = dt.maxVal fails) the query returns:

 Label      text
 ---------- ----------
 Adria      NULL

NOTE: Above queries (barring any cut-n-paste typos on my behalf) were run on ASE 15.7 SP140.

Kudos for the well structured answer. Yes, it's silly of me to have that MAX clause in the query (423 =423). However to answer your questions 1. Yes, it's possible to not find rows in A sometimes, Hence left join 2. As you can see from the sample data, 'Adria' has multiple mappings in A. I need to filter only on the max Val i.e., 568 hence the reason to use MAX. 3. Yes the data is huge, but the query is same as the one I have put here. — KnowNothing, Sep 21 '21 at 06:45
I have edited the sample data, just to give you an better idea about the data my tables has — KnowNothing, Sep 21 '21 at 06:51

Gordon Linoff · Answer 2 · 2021-09-20T11:55:38.130

-1

Your query should work, although the condition is redundant:

a.val = A.val

This is both referencing the table in the subquery. So, it is equivalent to 1=1. Actually, it is really equivalent to a.val is not null.

Because of this condition, I think you have made an error in transcribing your query into the question.

You can do what you want more simply using TOP:

select C.Label, A.txt
from C join
     B
     on B.Label = C.Label left join
     (select top (1) a.*
      from A
      order by val desc
     ) a
     on A.Val = B.Val 
where C.Label = 'Adria';

edited Sep 20 '21 at 11:55

answered Sep 20 '21 at 11:13

Gordon Linoff

1,242,037
58
646
786

I am getting an error for this answer Incorrect syntax near "." – KnowNothing Sep 20 '21 at 11:35
@KnowNothing . . . Except for the misspelling in the `where` clause -- which I'm fixing -- this code works as written (https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=658b2154a86257aed4ca051326c3db81). – Gordon Linoff Sep 20 '21 at 11:55
Strange it works in the fiddle and not in my DB. Is there any other method to achieve this without using top or limit? The reason being if i had same vals for two different texts, i would want to show all of them – KnowNothing Sep 20 '21 at 12:07
@KnowNothing . . . Perhaps the parentheses around `1` don't work in Sybase? – Gordon Linoff Sep 20 '21 at 12:29
I did try that, now it says "An order by clause isn't allowed in a derived table" very strange as top is used for exactly that. To use order by in a sub query – KnowNothing Sep 20 '21 at 12:41
@KnowNothing, You can use TOP and order by with your outer query as well. – Ankit Bajpai Sep 20 '21 at 13:04
@AnkitBajpai i won't be able to use WHERE IN clause if I use top in outer query. 'Adria' is a sample value – KnowNothing Sep 20 '21 at 13:09
@KnowNothing . . . Interesting. That is a very curious limitation in Sybase that I don't recall. – Gordon Linoff Sep 20 '21 at 15:56

Join on max value in SQL

2 Answers2