How Two Join to Tables Based on the Highest Value in One of Them

Getting max value from rows and joining to another table

What you wrote was missing A in the from clause so its not entirely clear where you went wrong but this should work

select 
       B.Id, 
       B.FileName,
       A.Name
FRom B
     INNER JOIN A
     ON A.id = B.id
    INNER JOIN ( 
          select A.Id, MAX(A.Rank)as ExpertRank 
          from A 
          group by A.Id
     ) as NewA 
    ON a.Id = NewA.ID 
       AND a.Rank = NewA.ExpertRank

See it working here

Alternatively you could use rownumber instead

WITH CTE AS 
(
   SELECT ID, 
          RANK,
          Name,
          ROW_NUMBER() OVER (PARTITION BY ID ORDER BY RANK DESC) rn
   FROM A
)
SELECT b.Id b.FileName,cte.Name
FROM
   b
   INNER JOIN cte 
   ON b.id = cte.id
      and cte.rn = 1

See it working here

join two tables taking the one with max value

Both your queries are okay for the task. None is really better than the other. But one may be faster than the other, which you can find out with EXPLAIN PLAN. Ideally Oracle would come up with the same execution plan for both, but it's a hard task for the optimizer to detect that both queries do the same thing.

As of Oracle 12c I'd use CROSS APPLY:

select u.*, a.id, a.address 
from my_user u
cross apply
(
  select *
  from my_address ma
  where ma.username = u.username
  order by ma.id desc
  fetch first row only
) a;

In earlier versions (as of Oracle 9i):

select u.*, a.id, a.address 
from my_user u
join
(
  select ma.*, row_number() over (partition by username order by id desc) as rn
  from my_address ma
) a on a.username = u.username and a.rn = 1;

In even earlier versions:

select u.*, a.id, a.address 
from my_user u
join
(
  select *
  from my_address ma
  where id in (select max(id) from my_address group by username)
) a on a.username = u.username;

Demo: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=c0d3ab6617956cb69f979a413026f6db

Join table with MAX value from another

The canonical way of approaching this is to use a subquery to identify the products and their maximum prices from the product_supplier table, and then to join this subquery to order to get the result set you want.

SELECT t1.orderID,
       t1.productID,
       COALESCE(t2.cost_price, 0.0) AS cost_price  -- missing products will appear
FROM order t1                                      -- with a zero price
LEFT JOIN
(
    SELECT productID, MAX(cost_price) AS cost_price
    FROM product_supplier
    GROUP BY productID
) t2
    ON t1.productID  = t2.productID AND
       t1.cost_price = t2.cost_price

Select the max value on a multiple table join

It seems you are looking for UNION ALL, so you can treat the data from different tables as if it were data from only one table:

SELECT 
  b.gid, 
  MAX(x.last_date) AS dateMax
FROM b
JOIN 
(
  SELECT geometry, last_date FROM a
  UNION ALL
  SELECT geometry, last_date FROM aa
  UNION ALL
  SELECT geometry, last_date FROM aaa
) x ON ST_Intersects(b.geometry, x.geometry)
GROUP BY b.gid;

Joining 2 tables on a specific key with selecting MAX(different_key) from one of them in MySQL

You want to bring the latest review of each sentence. One option uses a left join and window functions:

select s.*, sr.sentence_review
from sentence s
left join (
    select sr.*, row_number() over(partition by sentence_id order by id desc) rn
    from sentence_review sr
) sr on sr.sentence_id = s.sentence_id and s.rn = 1

This requires MySQL 8.0 though. In earlier versions, you can use a correlated subquery for filtering:

select s.*, sr.sentence_review
from sentence s
left join sentence_review sr 
    on  sr.sentence_id = s.sentence_id
    and sr.id = (
        select max(sr1.id)
        from sentence_review sr1
        where sr1.sentence_id = sr.sentence_id
    )

Select only rows by join tables max value

Whats wrong with:

select u.classno, u.userno, MAX(b.enddate)
from libUser u
join book b on b.id = u.bookid
group by u.classno, u.userno

Join two tables with highest ID in second table

Try this;)

SELECT table1.productname, table2.price
FROM table1
LEFT JOIN table2 ON table1.productname = table2.productname
WHERE (table2.productname, table2.ID) in (select productName, max(id) from table2 group by productName)

Joining tables based on the maximum id

You can add a derived table to reduce the matching rows in TABLE3 to one per group. Another method would use a window function but you asked for a JOIN only

SELECT
    table1.field1, table1.field2, table1.field3,
    table2.field1, table2.field2, table2.field3,
    table3.field1, table3.field2, table3.field3,
    table4.field1, table4.field2, table4.field3
FROM table1
INNER JOIN table2 ON
    table1.field1 = table2.field1
    AND table1.field2 = table2.field2
    AND table2.field3 < 0
INNER JOIN table3 ON
    table2.field1 = table3.field1
    AND table2.field4 = table3.field4

--here is the added derived table. Change column names as needed
INNER JOIN (select UID, ID = max(ID) from Table3 group by UID) x
    on x.UID = table3.UID and x.mx = table3.ID

INNER JOIN table4 ON
    table1.field1 = table4.field1
    AND table1.field2 = table4.field2

Or, perhaps... something like below. It really depends on your schema and that's hard to understand with the sample data.

INNER JOIN (select field1, field4, mx = max(ID) from Table3 group by field1, field4) x
    on x.field1 = table3.field1 and x.field4 = table3.field4 and x.mx = table3.ID

Here is an example. You'll notice that the last three column pairs are identical. You only want the last one, which is the max(id) for that grouping. What ever makes a row unique in relation to the rest of your data (not your primary key, but what you are joining with) is what you'd want to include int he derived table and join condition.

declare @table table (id int identity(1,1), f1 char(1), f2 char(1))
insert into @table
values
('a','b'),
('a','c'),
('a','a'),
('b','b'),
('b','b'),
('b','b')

select * from @table

select t1.*
from @table t1
inner join 
    (select f1, f2, mx = max(id) from @table group by f1, f2) t2 on
    t1.f1 = t2.f1
    and t1.f2 = t2.f2
    and t1.id = t2.mx

Joining a table with max value from another table

In Postgres, a relative simple method is a lateral join:

select ct.*, lt.status
from callTable ct left join lateral
     (select lt.*
      from logTable lt
      where lt.userId = ct.userId and lt.time <= ct.time
      order by lt.time desc
      fetch first 1 row only
     ) lt
     on lt.userId = ct.userId;

This can take advantage of an index on logTable(userId, time desc).

---

Related Topics