CODEWITHSUNDEEP
sqldb2greatest-n-per-group
rojobo
rojobo

Reputation: 486

SQL grouping. How to select row with the highest column value when joined. No CTEs please

I've been banging my head against the wall for something that I think should be simple but just cant get to work.

I'm trying to retrieve the row with the highest multi_flag value when I join table A and table B but I can't seem to get the SQL right because it returns all the rows rather than the one with the highest multi_flag value.

Here are my tables...

Table A

enter image description here

Table B

enter image description here

This is almost my desired result but only if I leave out the value_id row

SELECT CATALOG, VENDOR_CODE, INVLINK, NAME_ID, MAX(multi_flag) AS multiflag 
FROM TBLINVENT_ATTRIBUTE AS A
INNER JOIN TBLATTRIBUTE_VALUE AS B 
ON A.VALUE_ID = B.VALUE_ID
GROUP BY CATALOG, VENDOR_CODE, INVLINK, NAME_ID
ORDER BY CATALOG DESC

enter image description here

This is close to what I want to retreive but not quite notice how it returns unique name_id and the highest multi_flag but I also need the value_id that belongs to such multi_flag / name_id grouping...

If I include the value_id in my SQL statement then it returns all rows and is no longer grouped

Notic ein the results below how it no longer returns the row for the highest multi_flag and how all the different values for name_id (Ex. name_id 1) are also returned

enter image description here

Upvotes: 0

Views: 149

Answers (2)

Esperento57
Esperento57

Reputation: 17462

You can use Lateral too, its an other solution

  SELECT 
  A.CATALOG, A.VENDOR_CODE, A.INVLINK, B.NAME_ID, M.maxmultiflag 
  FROM TBLINVENT_ATTRIBUTE AS A
  inner join lateral
  (
   select max(B.multi_flag) as maxmultiflag from TBLINVENT_ATTRIBUTE C
   where A.VALUE_ID = C.VALUE_ID
  ) M on 1=1
  INNER JOIN TBLATTRIBUTE_VALUE AS B ON M.maxmultiflag = B.VALUE

Upvotes: 0

Tywan Terrell
Tywan Terrell

Reputation: 39

You can choose to use a sub-query, derived table or CTE to solve this problem. Performance will be depending on the amount of data you are querying. To achieve your goal of getting the max multiflag you must first get the max value based on the grouping you want to achieve this you can use a CTE or sub query. The below CTE will give the max multi_flag by value that you can use to get the max multi_flag and then you can use that to join back to your other tables. I have three joins in this example but this can be reduce and as far a performance it may be better to use a subquery but you want know until you get the se the actual execution plans side by side.

;with highest_multi_flag as ( select value_id, max(multi_flag) AS multiflag FROM TBLINVENT_ATTRIBUTE group by value_id ) select A.CATALOG, a.VENDOR_CODE, a.INVLINK, b.NAME_ID,m.multiflag from highest_multi_flag m inner join TBLINVENT_ATTRIBUTE AS A on a.VALUE_ID =b. m.VALUE_ID INNER JOIN TBLATTRIBUTE_VALUE AS B ON m.VALUE_ID = B.VALUE

Upvotes: 1

Related Questions