Keep Records Conditioned on Other Columns

Database

Keep Records Conditioned on Other Columns

Hi All,

The data I have look like this:

1.PNG

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!

 


Accepted Solutions
N/A

Re: Keep Records Conditioned on Other Columns

Another solution based on conditional aggregation:

select * from tab
qualify
   max(case when Date_Revision = timestamp '2016-04-29  20:37:35' then 1 end)
   over (partition by itemid) = 1

 

1 ACCEPTED SOLUTION
6 REPLIES
N/A

Re: Keep Records Conditioned on Other Columns

Hi Lorraine,

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 ;

 

Pierre

Re: Keep Records Conditioned on Other Columns

Hi Pierre,

 

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.

N/A

Re: Keep Records Conditioned on Other Columns

Lorraine,

 

you can try this:

select T1.*,

 

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

;

Pierre

 

N/A

Re: Keep Records Conditioned on Other Columns

Another solution based on conditional aggregation:

select * from tab
qualify
   max(case when Date_Revision = timestamp '2016-04-29  20:37:35' then 1 end)
   over (partition by itemid) = 1

 

N/A

Re: Keep Records Conditioned on Other Columns

Yes Dieter,

more concise and effective !

 

Re: Keep Records Conditioned on Other Columns

That works like charm, thanks a lot!