Query that returns grouped data
I am having troubles writing a query that would only return results where a certain value differs from the previous record based on partitioned data and a timestamp
My simple table is
ROWID STORE DATE CATEGORY
1 1000 01/01/2007 B
2 1000 02/02/2007 B
3 1000 03/02/2007 A
4 1001 02/02/2007 B
5 1001 02/02/2007 B
6 1002 03/02/2007 B
7 1002 03/02/2007 A
What I want is in my query result is the data for ROWID 3,2 & 7,6 as only those rows have changes in category for a given store
Thanks
I dare to try to answer :-))
mozart: you can add a filter to the inner query, e.g.
Code:
...
from simple
where store in
(
select store
from simple
where ts between date1 and date2
group by store
having count(distinct category)>1
)