SQL find group wise max date wise
SQL find group wise max date wise
提问于 2020-06-08 03:04:06
我想找到每个项目的位置与最大日期的stock_actual，但显示所有日期行
SELECT
MAX(date), Item, Location, Stock_Actual
FROM
INV_Movement
WHERE
data <= '2020-05-31'
GROUP BY
Item, Location, Stock_Actual
ORDER BY
Location回答 3
Stack Overflow用户
发布于 2020-06-08 03:38:24
首先是，你必须找到那一行，然后是JOIN。您可以在OUTER APPLY中使用TOP 1来实现这一点
SELECT
im.date,
im.Item,
im.Location,
im.Stock_Actual
FROM INV_Movement im
OUTER APPLY (SELECT TOP 1 im1.*
FROM INV_Movement im1
WHERE im1.item = im.item AND im1.location = im.location
ORDER BY im1.date DESC) im1
WHERE im.date <= '2020-05-31'
AND im.item = im1.item
AND im.location = im1.location
AND im.date = im1.date注意：date列看起来像datetime，并且在where子句中使用date进行比较。它可能会改变结果。
Stack Overflow用户
发布于 2020-06-08 03:41:54
谢谢大家，我自己用谷歌搜索参考得到了答案。
答案是
SELECT A.data, A.Item, A.location, A.stock_actual
FROM INV_Moviment A
INNER JOIN (
SELECT Item, MAX(date) AS DT, Location
FROM INV_Movimentos where data <= '2020-05-31'
GROUP BY Item, Location
) AS B ON A.Item = B.Item AND A.Date = B.DT
order by Location, ItemStack Overflow用户
发布于 2020-06-08 03:53:33
没有使用排名的自连接的简单解决方案：
with cte as
(
SELECT data, Item, location, stock_actual,
rank() -- rank per item based on descending date
over (partition by Item
order by date desc) as rnk
FROM INV_Movimentos
where date <= '2020-05-31'
)
select *
from cte
where rnk = 1
order by Location,Item页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/62250289
复制相关文章
相似问题
腾讯云开发者
