Record Count - inclued first instance/exclued future instances

[acct098]

Does anyone know how to write a script to count a record (T_NUM) once so that it is capture in the stats for the first date and excluded from all future dates?



T_NUM EDATE
------------ ---------
797PR6005 05-DEC-06
797Q70015 05-DEC-06
797Q70015 06-DEC-06
797Q70015 07-DEC-06
797S70013 05-DEC-06
797S70013 06-DEC-06
797S70013 07-DEC-06

 

[LKBrwnDBA]

What about:

Select T_NUM, MAX(EDATE) From MyTable
 Group By T_NUM;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[acct098]

When I try the max(edate) function it get the following error:

SQL> select u.EDATE, u.USERID_2,
2 COUNT(CASE WHEN CLEARED IS NULL
3 THEN 'X'
4 ELSE NULL END) A ,
5 COUNT(CASE WHEN CLEARED = 'N'
6 THEN 'X'
7 ELSE NULL END) B ,
8 COUNT(*) AS TOTAL
9 FROM SUSF u
10 WHERE USERID_2 = 'S796MRR' AND
11 MAX(EDATE) From
12 EXISTS (SELECT 'x' FROM SUSF q
13 WHERE q.USERID_2||q.B_C||q.B_AO||q.B_NUM||q.T_C||q.AO||q.T_NUM
14 = u.USERID_2||u.B_C||u.B_AO||u.B_NUM||u.T_C||u.AO||u.T_NUM)
15 GROUP BY USERID_2, EDATE
16 ORDER BY USERID_2, EDATE;
MAX(EDATE) From
*
ERROR at line 11:
ORA-00934: group function is not allowed here


Elapsed: 00:00:00.03

 

[LKBrwnDBA]

Your SQL statement is malformed here:

...
10  WHERE USERID_2 = 'S796MRR' AND 
 11      MAX(EDATE) From   
 12      EXISTS (SELECT 'x' FROM SUSF q
 13               WHERE USERID_2||q.B_C||q.B_AO||q.B_NUM||q.T_C||q.AO||q.T_NUM
 14 = u.USERID_2||u.B_C||u.B_AO||u.B_NUM||u.T_C||u.AO||u.T_NUM)  
 15  GROUP BY USERID_2, EDATE
...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb