Avatar of cynx
cynxFlag for India asked on

SQL Query for max of sum and look up

I have two tables (Table 1 & Table 2) as below

Table 1
ID      Code      V1      V2
--                  ------             ---                  ----
1      E-1      2      1
1      E-12      1      2
1      E-10      3      1
1      E-9      3      2
1      E-8      5      8
1      E-3      8      8
2      E-3      1      3
2      E-5      2      4

Table 2
Code      Desc
-----              -------
E-1      asd
E-2      dsa
E-3      ad
E-4      dd
E-5      aadas
E-6      asd
E-7      asdddd
E-8      asds
E-9      sds
E-10      qwqwe
E-11      da
E-12      cccc

For each ID in Table 1 , i need to get a Desc from table 2 which works as below

In Table 1, for each ID, I need to select a 'Code' which was maximum sum of V1 and V2

for that Code, I need the DESC.

How can i workout the query for this ?
MySQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
cynx

8/22/2022 - Mon
ASKER
cynx

just to give an example, for ID 1 in table 1, E-3 will be selected, as V1&V2 sum is maximum (16) for ID 1
and the desc will be 'ad'
ralmada

try

select a.id, a.code, a.v1, a.v2, b.desc
from table1 a
inner join table2 b on a.code = b.code
where a.v1 + a.v2 = (select max(v1 + v2) from yourtable where id = a.id)
ASKER
cynx

guess i missed out one point, the CODE in table 2 is 1,2,3,4,5....
where as in Table 1 it is E-1-2,E-12-23, hence i need to compare it with middle value of E-1-2, so it will be 1 in this case.
Since the lenght of string is not fixed, as it can be E-1-2 or E-12-14, in these cases i need to compare with 1 and 12, how do i get the middle number, as substring function wont help in this case ?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
cynx

something like PATINDEX but to search from right to left ?
ASKER CERTIFIED SOLUTION
ralmada

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
cynx

sorry i am not able to execute the query, yourtable will be Table1  ?
I dont get any errors, but no resutls returned
ASKER
cynx

guess the LIke part is not returning anything
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
cynx

wont this be other way round ?
b.code like '%-' + cast(a.code as varchar) + '-%'
as
a.code like '%-' + cast(b.code as varchar) + '-%'
ralmada

well if the sample data is like this

Table 1
ID      Code      V1      V2
--                  ------             ---                  ----
1      1      2      1
1      12      1      2
1      10      3      1
1      9      3      2
1      8      5      8
1      3      8      8
2      3      1      3
2      5      2      4

Table 2
Code      Desc
-----              -------
E-1-1     asd
E-2-13      dsa
E-3-12     ad
E-4-1      dd
E-5-6      aadas
E-6-14      asd
E-7-55      asdddd
E-8-8      asds
E-9-1      sds
E-10-1      qwqwe
E-11-1     da
E-12-2      cccc

Then it should be

select a.id, a.code, a.v1, a.v2, b.desc
from table1 a
inner join table2 b on b.code like '%-' + cast(a.code as varchar) + '-%'
where a.v1 + a.v2 = (select max(v1 + v2) from yourtable where id = a.id)

now I wonder what datatype is code on table1? I've assumed it's int, but if it's varchar it might be an issue with some extra spaces there, so you might want to use ltrim

select a.id, a.code, a.v1, a.v2, b.desc
from table1 a
inner join table2 b on b.code like '%-' + rtrim(ltrim(a.code)) + '-%'
where a.v1 + a.v2 = (select max(v1 + v2) from yourtable where id = a.id)
ASKER
cynx

Sorry for the confusion, the data in table 2 was with only numeric codes.
Hence reversing the statement worked.
Like statement did work, as it was issue with above.

the another issue i had was summation of values if either of V1 or V2 was NULL. hence i used isnull() to address this.

Thanks for your help to solve this.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
cynx

brilliant !