获取每个项目的最新记录 - 帮助 [英] Get the latest record per item- help
问题描述
大家好 - 我有一个让我疯狂的sql谜语。
我有2个桌子
项目和itemposts
我想在item_no上联合它们,然后从每个项目的itempost表中获取最新的itempost,posting_id。
每个posting_id都有一个created_date 。
我希望获得一个包含每个item_no的最新posts_id的列表。每个item_no都连接了几个posts_id,但我只想要最新的。
(语法不正确,但显示要点)
第一步是执行以下操作:
选择 item_no,max(created_date) as ' created_date' 来自 itemposts group by item_no
现在,对于每个item_no,您都拥有了想要itempost记录的日期。
一旦你有了这个,就可以围绕第一个查询构建一个查询(使用第一个查询作为子查询)来获得你想要的东西,或多或少是这样的:
选择
Item.item_no,
itemp osts.posting_id,
来自
item
join itemposts 上的class =code-keyword>(item.item_no = itemposts.item_no)
join (
选择
item_no,
max(created_date) ' created_date'
from
itemposts
< span class =code-keyword> group by
item_no
)sq on
(sq.item_no = itemposts.item_no)和
(itemposts.created_date = sq.created_date)
Hello everyone - i have a sql riddle that is driving me crazy.
I have 2 Tables
Item and itemposts
I want to joint them on item_no and then get the latest itempost, posting_id from the itempost table for each item.
Each posting_id has a created_date.
My hope will be to get a list containing the latest posting_id per item_no. Each item_no have several posting_id's connected but i only want the latest.
(Not syntactically correct, but shows the point)
First step would be to do the following:
select item_no, max(created_date) as 'created_date' from itemposts group by item_no
Now you have, for every item_no, the date for which you want the itempost record.
Once you have that, you can build a query around the first query (Using the first query as a subquery) to get what you want, more or less like this:
Select Item.item_no, itemposts.posting_id, from item join itemposts on (item.item_no = itemposts.item_no) join ( select item_no, max(created_date) as 'created_date' from itemposts group by item_no ) sq on (sq.item_no = itemposts.item_no) and (itemposts.created_date = sq.created_date)
这篇关于获取每个项目的最新记录 - 帮助的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!