Jinish Jinish - 19 days ago 5
SQL Question

SQL select conditions

I have a table that contains data in the following structure:

Id | OperationType | ObjectName | dt_created
-- | ------------- | ---------- | ----------
1 | 4 | test.com | 2015-08-30 23:23:57.000
2 | 7 | test.com | 2015-08-30 23:23:57.000
3 | 17 | test.com | 2015-08-30 23:23:57.000
4 | 26 | test.com | 2015-08-30 23:23:57.000
5 | 8 | test.com | 2015-08-30 23:23:57.000
6 | 4 | test.com | 2015-08-30 23:23:57.000
7 | 17 | 123.com | 2015-08-30 23:23:57.000
8 | 18 | 123.com | 2015-08-30 23:23:57.000
9 | 26 | 123.com | 2015-08-30 23:23:57.000
10 | 8 | 123.com | 2015-08-30 23:23:57.000


I want to get the ID's of the records where there is an operation type 17 followed by 26

I have tried a few approaches like:


select abc.id, abc.PreviousOperationType
from (select id,
case
when OperationType = 26
then
lead(OperationType, 1, 0) over (partition by id order by id)
else
null
end as PreviousOperationType
from operation
where dt_created between '2015-09-20' and '2015-09-30') as abc
where abc.PreviousOperationType is not null and abc.PreviousOperationType= 17


but not able to get accurate results.

Any help would be appreciated.

Thanks,
J

Answer

The following query gives you ID 3, because it is of type 17 and is followed by a record of type 26.

select id
from
(
  select 
    id,
    operationtype,
    lead(operationtype) over (order by dt_created) as next_operationtype
  from operation
) op
where operationtype = 17 and next_operationtype = 26;
Comments