Query that returns grouped data

[cjard]

Analytic functions may help you.

See http://download-uk.oracle.com/docs/c...nctions001.htm

select store, ts, category, prev_category, next_category
from
(
   select  store, ts, category,
           lag(category,1,category) over(partition by store order by ts) as prev_category,
           lead(category,1,category) over(partition by store order by ts) as next_category
   from simple
)
where category!=prev_category or category!=next_category

you forgot to do store code checking too.. your query will e.g. return rowid 4, because it has a different category to rowid 3

you need to say:

select store, ts, category, prev_category, next_category
from
(
   select  store, ts, category,
           lag(category,1,category) over(partition by store order by ts) as prev_category,
           lead(category,1,category) over(partition by store order by ts) as next_category,
           lag(store,1,store) over(partition by store order by ts) as prev_store,
           lead(store,1,store) over(partition by store order by ts) as next_store
   from simple
)
where (category <> prev_category and store = prev_store) or (category <> next_category and store = next_store)

also, i swapped your != for <> because I didnt know oracle did != for not equals

I dont know why the timestamp column is mentioned, it doesnt seem to be used by the OP (other than an order by for the partition?)