CODEWITHSUNDEEP
sqldatabaseoracle-databaseselect
SSV
SSV

Reputation: 860

Find tasks with one value that are earlier than the earliest task with another value

I've been trying to forge a select which will capture table rows with service_name column ="VALUE1" being modified earlier than the earliest row with service_name column = "VALUE2", where their task_id column is equal

So far I have everything but the last condition

select 
  * 
from 
  test s1 
where (s1.service_name='VALUE1' 
      and s1.X_MODIFY_DATE < (select 
                                * 
                              FROM (select modify_date 
                                   from test s2 
                                   where s2.service_name='VALUE2' 
                                   order by s2.modify_date desc) 
                              where rownum <2)) ;

Obviously, adding where s1.task_id = s2.task_id won't work.

Is there any simple way to achieve my goal?

Upvotes: 0

Views: 47

Answers (2)

David Faber
David Faber

Reputation: 12485

You might try the following:

WITH s2 AS (
    SELECT task_id, MIN(x_modify_date) AS min_modify_date
      FROM test
     WHERE service_name = 'VALUE2'
     GROUP BY task_id
)
SELECT s1.* FROM test s1, s2
 WHERE s1.service_name = 'VALUE1'
   AND s1.task_id = s2.task_id
   AND s1.x_modify_date < s2.min_modify_date;

Here I am using the WITH clause to create an alias for a subquery giving the minimum modify date by task_id. One caveat is that no records will be returned if there aren't any 'VALUE2' records for a given task_id in TEST.

Upvotes: 1

crthompson
crthompson

Reputation: 15875

Based on your description, I would think that this should accomplish your goal:

select
  s1.*
from
  test s1
  inner join (select 
                min(X_MODIFY_DATE) as X_MODIFY_DATE, service_name, task_id
              from
                test
              where service_name = 'VALUE2'
              group by service_name, task_id) s2 on s1.X_MODIFY_DATE > s2.X_MODIFY_DATE 
                                                and s1.service_name = 'VALUE1' 
                                                and s1.task_id = s2.task_id

This will get all the s1 records that have a modify date less than the earliest s2 date, but still have the same task_id with the values set for each.

Upvotes: 1

Related Questions