Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Distinct combination of Data

NavneetUMar 15 2010 — edited Mar 15 2010
Hi All,

I just want a little help from you.

I am having a table 
SQL> select * from test;

        ID          A          B
---------- ---------- ----------
         1          1          2
         2          2          1
         3          1          3
         4          3          2
i want a query that should retrieve me the following records..
SQL> select * from test;

        ID          A          B
---------- ---------- ----------
         1          1          2
         3          1          3
         4          3          2
Can you please help me to get this done?

Regards
This post has been answered by ravikumar.sv on Mar 15 2010
Jump to Answer

Comments

Centinul
Give this a shot:
SELECT action
     , COUNT(*) AS cnt
FROM   ( SELECT action
              , date_time - LAG(date_time,1,date_time - 2) OVER (PARTITION BY action ORDER BY date_time) AS time_diff
         FROM   log_table
         /* Month filtering here */
         WHERE  date_time BETWEEN TO_DATE('04/01/2011','MM/DD/YYYY') AND TO_DATE('04/30/2011','MM/DD/YYYY')
       )
WHERE  time_diff >= 2
GROUP BY action
ORDER BY action
;
Aketi Jyuuzou 
with t(ACTION,DATE_TIME) as(
select 1,date '2011-03-31' from dual union all
select 1,date '2011-04-01' from dual union all
select 2,date '2011-04-01' from dual union all
select 1,date '2011-04-04' from dual union all
select 1,date '2011-04-05' from dual union all
select 2,date '2011-04-07' from dual union all
select 2,date '2011-04-08' from dual union all
select 2,date '2011-04-10' from dual)
select ACTION as NEW_ACTION,sum(willSum) as cnt
from (select ACTION,
      case when Lag(DATE_TIME) over(partition by ACTION
                order by DATE_TIME)+2 > DATE_TIME
           then 0 else 1 end as willSum
      from t
      where trunc(sysdate,'mm') <= DATE_TIME)
group by ACTION;

NEW_ACTION  CNT
----------  ---
         1    2
         2    3
My SQL articles of OTN-Japan :-)
http://www.oracle.com/technetwork/jp/articles/otnj-sql-image3-1-323602-ja.html
781721
Many thank you for the replies!

Probably I did not explain my problem correctly in my initial post... I tried your solutions but it only addresses only one part of my problem.

The purpose of my report query -> A user will be charged/billed for each distinct new ACTION at the begin of each month. A charged/billed ACTION is like a token that is valid for 2 days. The user then has 2 days to repeat the same action as many times as he wants (for free). Afterwards (once the token expired) he will be charged/billed again for the same next ACTION.

-> From my log table data I need to calculate how many "tokens" I need to charge/bill the user for the past month...

Maybe this can not be implemented in one single SQL query !?

Thx & cheers,
Peter
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 12 2010
Added on Mar 15 2010
13 comments
1,455 views