CODEWITHSUNDEEP
sqloracle-databasesubquerycorrelated-subquery
Khurram Majeed
Khurram Majeed

Reputation: 2391

subquery in oracle 10g

I have a table in oracle 10g database with following columns

sdate                  sid    event  
31/10/2013 20:20:20     1        A    
31/10/2013 20:20:21     1        B   
31/10/2013 20:20:22     1        C   
31/10/2013 20:41:04     2        A    
31/10/2013 20:41:10     2        B   
31/10/2013 20:42:20     2        C    
31/10/2013 20:42:49     2        C   
31/10/2013 21:20:50     3        A   
31/10/2013 21:21:33     3        B   
31/10/2013 21:23:00     3        C   
31/10/2013 21:08:20     4        B    
31/10/2013 21:11:20     4        C  
31/10/2013 21:17:20     4        C 
31/10/2013 22:20:20     5        C 
31/10/2013 22:22:22     5        D

sdate is the date of this record, sid is like a session ID, event is unique event within a session e.g. A, B , C etc. A session should always start with one occurrence of event 'A'. Other events like B, C can repeat.

Can some one please help me to write a query that will give me a list of all SID, which start with an event other than 'A' for example sid 4 and 5 because tese sessions start with B and C.

UPDATE: I have abbreviated events to A,B,C the actual events in my table a long strings.

Upvotes: 0

Views: 189

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can use analytic functions for this purpose:

select t.*
from (select t.*, row_number() over (partition by sid order by sdate) as seqnum
      from table t
     ) t
where seqnum = 1 and event <> 'A';

row_number() enumerates the events for each sid based on the date. The where clause chooses the first one and all such rows where the event is not an 'A'.

Upvotes: 1

Related Questions