select specific tranactions for a member

I want to be able to select for a member their first transaction for each day and only for the first 3 days and them place an "order" against each as shown in exam开发者_Go百科ple?

Member     tranDate trantime              order
---------------------------------------------------------------
member 1   05/03/11 11:03:22.000          1            selected
member 1   05/03/11 13:03:22.000
member 1   07/03/11 13:03:22.000
member 1   06/03/11 13:03:22.000          2            selected
member 1   08/03/11 13:03:22.000
member 1   07/03/11 12:03:22.000          3            selected

---

using Oracle in my example :

with tab as (
select 'member 1' as member, to_date('05/03/11', 'DD/MM/YY') as trandate, '11:03:22' as trantime from dual
union all
select 'member 1' as member, to_date('05/03/11', 'DD/MM/YY') as trandate, '13:03:22' as trantime from dual
union all
select 'member 1' as member, to_date('07/03/11', 'DD/MM/YY') as trandate, '13:03:22' as trantime from dual
union all
select 'member 1' as member, to_date('06/03/11', 'DD/MM/YY') as trandate, '13:03:22' as trantime from dual
union all
select 'member 1' as member, to_date('08/03/11', 'DD/MM/YY') as trandate, '13:03:22' as trantime from dual
union all
select 'member 1' as member, to_date('07/03/11', 'DD/MM/YY') as trandate, '12:03:22' as trantime from dual )

SELECT member,
       trandate,
       trantime,
       CASE
         WHEN (dense_rank()
          over(PARTITION BY member ORDER BY trandate) < 4 AND dense_rank() over(PARTITION BY member,trandate ORDER BY trantime) = 1) THEN
          dense_rank() over(PARTITION BY member ORDER BY trandate)
         ELSE
          NULL
       END
  FROM tab  

output

MEMBER                   TRANDATE    TRANTIME   order
------------------------ ----------- ---------- -----
member 1                 05/03/2011  11:03:22       1
member 1                 05/03/2011  13:03:22   
member 1                 06/03/2011  13:03:22       2
member 1                 07/03/2011  12:03:22       3
member 1                 07/03/2011  13:03:22   
member 1                 08/03/2011  13:03:22   

6 rows selected