CODEWITHSUNDEEP
sqldb2
Tejus
Tejus

Reputation: 83

Is it possible from a DB2 (or any other sql) query to fetch rows selectively as shown below?

TIME STATUS  
9:00 Success  
8:00 Success  
7:00 Fail  
6:00 Fail  
5:00 Fail  
4:00 Success  
3:30 Fail
3:00 Success
...  ...

The table consists of plenty of records. The TIME column is actually a timestamp, but for simplicity I have just provided h:mm format.

My requirement is to fetch the first set of all those records whose STATUS column value lies between two 'Success' values when the data in the table is ordered descending on Time as shown.

So in the above case the query should fetch 3,4,5 records. i.e., 

TIME STATUS
7:00 Fail  
6:00 Fail  
5:00 Fail

Another example:

TIME STATUS  
9:00 Success  
8:00 Success  
7:00 Success  
6:00 Fail  
5:00 Fail  
4:00 Fail  
3:30 Fail
3:00 Success
2:30 Fail
2:15 Fail
2:12 Fail
2:00 Success
...  ...

In the above case the query should fetch 4,5,6,7 records which is the first set of 'Fail's lying between two 'Success'es. i.e.,

TIME STATUS
6:00 Fail  
5:00 Fail  
4:00 Fail  
3:30 Fail

Upvotes: 1

Views: 55

Answers (1)

valex
valex

Reputation: 24144

First you should find the first FAIL time in the table. Then you should select each FAIL earlier than the first and there are no any SUCCESS rows from the current and to the first FAIL in the table.

Here is query in standard SQL:

SELECT * FROM T AS T1 
  WHERE STATUS='fail'
    AND NOT EXISTS (SELECT * FROM T 
                       WHERE STATUS='Success'
                             AND TIME>T1.TIME 
                             AND TIME<=
                              (
                                SELECT TIME FROM T as T2
                                       WHERE STATUS='fail'
                                       AND NOT EXISTS (SELECT * FROM T 
                                                         WHERE TIME>T2.TIME 
                                                            AND STATUS='Fail')

                              )
                     )

SQLFiddle demo

Upvotes: 1

Related Questions