Query that returns grouped data

[mozart]

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

[ales]

Analytic functions may help you.

See http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.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

[tabreaz]

Try this query it should be helpful


SELECT * FROM STORES
WHERE ROWID IN
(
SELECT MAX(ROWID) OVER(PARTITION BY STORE, CATEGORY)
FROM STORES
)

[cjard]

Analytic functions may help you.

See http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.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?)

[mozart]

Thanks for those responses.

My next question is for Cjards query that worked well.

The question is how do I now limit that result set so that it only returns a result set for those stores who have had category changed between two dates say as weeks worth.

What this means is that the date parameter can only be applied to the very latest updates however we still want to see the history and do not want it filtered out.

Stores that have had their last update made outside of this 'week' should not appear.

[ales]

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?)
Did you test the query or it's just a guess?
Rowid 3 and 4 have different stores and the aren't compared because of the "partition by store" clause.

Can you see the row_id 4 in the result set?


create table simple (row_id number, store char(4), ts date, category char);
insert into simple values(1,'1000',to_date('01/01/2007','dd/mm/yyyy'),'B');
insert into simple values(2,'1000',to_date('02/02/2007','dd/mm/yyyy'),'B');
insert into simple values(3,'1000',to_date('03/02/2007','dd/mm/yyyy'),'A');
insert into simple values(4,'1001',to_date('02/02/2007','dd/mm/yyyy'),'B');
insert into simple values(5,'1001',to_date('02/02/2007','dd/mm/yyyy'),'B');
insert into simple values(6,'1002',to_date('03/02/2007','dd/mm/yyyy'),'B');
insert into simple values(7,'1002',to_date('03/02/2007','dd/mm/yyyy'),'A');
commit;

select * from simple order by row_id;

ROW_ID STOR TS C
---------- ---- ------------------- -
1 1000 01.01.2007 00:00:00 B
2 1000 02.02.2007 00:00:00 B
3 1000 03.02.2007 00:00:00 A
4 1001 02.02.2007 00:00:00 B
5 1001 02.02.2007 00:00:00 B
6 1002 03.02.2007 00:00:00 B
7 1002 03.02.2007 00:00:00 A

7 rows selected.

select row_id, store, ts, category, prev_category, next_category
from
(
select row_id, 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
/

ROW_ID STOR TS C P N
---------- ---- ------------------- - - -
2 1000 02.02.2007 00:00:00 B B A
3 1000 03.02.2007 00:00:00 A B A
6 1002 03.02.2007 00:00:00 B B A
7 1002 03.02.2007 00:00:00 A B A


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?)
It's only for checking purpose. I posted a proposal, not a complete solution.

[ales]

mozart: you can add a filter to the inner query, e.g.
...
from simple
where store in
(
select store
from simple
where ts between date1 and date2
group by store
having count(distinct category)>1
)