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

Analytic functions may help you.

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

Code:

---

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

---

Try this query it should be helpful

Code:

---

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

---

Quote:

---

Originally Posted by ales

Analytic functions may help you.

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

Code:

---

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:

Code:

---

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?)

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.

Quote:

---

Originally Posted by cjard

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?

Code:

---

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

---

Quote:

---

Originally Posted by cjard

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.

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
)

---