The data I have look like this:
I want to keep the records belonging to the item_id whose 'Date_Revision' includes a certain value, say '4/29/2016 8:37:35 PM'. Thus in this example, I want to keep the first 4 rows which belong to item_id '331314221355', and one of its 'date_revision' is '4/29/2016 8:37:35 PM'.
Any suggestion is welcomed! Thanks a lot!
Solved! Go to Solution.
If the question is to extract all rows for Items whose at least on revised date is 4/29/2016 you have to:
first read table T1 to get the list L1 of Items whose at least on revised date is 4/29/2016,
next read table T1 to extract rows for Items belonging to the list L1.
An answer with inner join:
select T1.* from "your table" T1
inner join (select distinct Item_ID from "your table" where date_revised = '2016-04-29') T2
on T1.Item_ID = T2.Item_ID ;
Thanks for your reply. Actuallly what you suggested is the method I'm using now. But I'm wondering whether there are other methods which can achieve the same result in one query, instead of using join function.
you can try this:
ABS(revised_date (date, format 'yyyy-mm-dd')) - ('2016-04-29' (date, format 'yyyy-mm-dd'))) Diff -- Avoiding negative figures
, min(Diff) over(partition by Item_ID) (named "the lowest diff.") -- just for checking
from "your table" T1
qualify min(Diff) over(partition by Item_ID) = 0