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
LEFT JOIN on Max Value
Try something like this:
SELECT
s.*,
ss.*
FROM
student AS s
LEFT JOIN
student_story AS ss
ON (ss.studentid = s.studentid)
WHERE ss.dateline = (
SELECT
MAX(dateline)
FROM
student_story AS ss2
WHERE
ss2.studentid = s.studentid
)
How to select max value from rows and join to another table
Below is for BigQuery Standard SQL
#standardsql
select as value array_agg(struct(id, value, name) order by value desc limit 1)[offset(0)]
from
(
select * from `project.dataset.order`
union all
select * from `project.dataset.product`
)
group by id
with output
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 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
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)
.
SQL select rows in a JOIN with max value on a column
As expected, aggregate functions work on one column without considering other columns, they are not filters.
That's why the MAX
function returns the maximum value met in the designated column, but others column are not the ones that correspond to the max value selected (or any result of an aggregate function).
In order to select the matching columns based on the max value, we can use a JOIN
query, in our case, joining both on order_id
and price
.
SELECT
ID,
post_date,
wp_woocommerce_order_items.order_item_name,
wp_woocommerce_order_itemmeta.meta_value
FROM wp_posts
JOIN wp_woocommerce_order_items
ON ID = order_id
JOIN wp_woocommerce_order_itemmeta
ON wp_woocommerce_order_items.order_item_id = wp_woocommerce_order_itemmeta.order_item_id
JOIN (
SELECT
order_id,
MAX(meta_value) as price
FROM wp_woocommerce_order_items
JOIN wp_woocommerce_order_itemmeta
ON wp_woocommerce_order_items.order_item_id = wp_woocommerce_order_itemmeta.order_item_id
WHERE meta_key = '_line_subtotal'
GROUP BY order_id
) b
ON ID = b.order_id AND wp_woocommerce_order_itemmeta.meta_value = price
WHERE
post_type = 'shop_order'
AND post_status = 'wc-completed'
AND meta_key = '_line_subtotal';
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
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
Extract a max value from a query with joins
I think you can use dense_rank()
or rank()
and some additional filtering:
select r.*
from (select r.id, u.reminder_id, r.name, r.remark, u.user_id,
dense_rank() over (order by u.reminder_id desc) as seqnum
from REMINDER_USERS u join
REMINDER r
on r.id = u.reminder_id join
DEVICE d
on d.id = (regexp_replace(r.origin_values, '[^0-9]', ''))
where r.name like '%Interne%' and
r.name <> 'Interne_2379'
) r
where seqnum = 1;
Note: If you actually want the most recent reminder per user, then use:
dense_rank() over (partition by u.user_id order by u.reminder_id desc) as seqnum
Related Topics
Removing Duplicate Rows (Based on Values from Multiple Columns) from SQL Table
How to Join Two Recordset Created from Two Different Data Source in Excel Vba
SQL Server Unique Composite Key of Two Field with Second Field Auto-Increment
How to Improve Performance for Datetime Filtering in SQL Server
How to Pass a Temp Table as a Parameter into a Separate Stored Procedure
"Order By" Using a Parameter for the Column Name
SQL Server - Lack of Natural Join/X Join Y Using(Field)
Base 36 to Base 10 Conversion Using SQL Only
Prepared Statement on Postgresql in Rails
Getting Warning: Null Value Is Eliminated by an Aggregate or Other Set Operation
The Conversion of a Datetime2 Data Type to a Datetime Data Type Resulted in an Out-Of-Range
Opinions on Sensor/Reading/Alert Database Design
SQL Server: Find Out Default Value of a Column with a Query
Check If Null Exists in Postgres Array
Return Rows in the Exact Order They Were Inserted
How to Execute a Native SQL Script in JPA/Hibernate
Postgresql: Give All Permissions to a User on a Postgresql Database