How to Compare Rows in SQL?

Forming a SQL query to compare results across rows

• Problem statement: select all stores name , their status, phone numbers , effective date whose phone number has been changed from 2003 until present date. Schema is store_name,phone number , start_date , status sample rows abc 1234 30-DEC-2011 open abc 3433 04-Jan-2012 close bbb 4444 30-Jan-2010 open bbb 4444 31-Jan-2011 open Output abc 1234 open 30-DEC-3011 till 3-Jan-2012 abc 3433 close 04-Jan-2012 till date I am also fine having two rows in output with sorted start date like abc 1234 30-DEC-2011 open abc 3433 04-Jan-2012 close bbb should not be reported as there was no change in phone number. We should report only those stores for which phone number was changed . Can someone help me with this query on Oracle? I guess by using correlated queries it can be done but I am not sure how can I construct one. Please note that my table is having around 3154953 records so I also need to make sure that correlated query doesn't lock the table for whole lot of time. Is this even possible with Oracle ? Thanks! APC's answer works for me just that I am seeing alot of repetitions in my result. For input : select store_name,phone_number,start_date, status where store_name=abc; returns STORE_name Phone number start_date STATUS ---------------- ---------------- ----------- ---------- abc 122 18-JAN-2011 open abc 122 18-JAN-2011 open abc 122 18-JAN-2011 close running your query gives me following output. abc 122 open from 18-JAN-2011 to 17-JAN-2011 abc 122 open from 18-JAN-2011 to 17-JAN-2011 abc 122 close from 18-JAN-2011 to date Can you explain why and where is the miss?

• Answer:

  I'm presuming that this is for Oracle rather than MySQL, as my solution uses a couple of magic tricks which I'm pretty certain are not available in MySQL. The first is the Common Table Expression to get a result set which we can use more than once. The second is the use of the LEAD() analytic function to "predict" values in the next row. So, here's the query: with a as ( select store_name , phone_number , status , start_date , lead (start_date, 1, trunc(sysdate)) over (partition by store_name order by start_date) as next_date , lead (phone_number, 1, null) over (partition by store_name order by start_date) as next_number from your_table where start_date >= date '2003-01-01' ) select a.store_name , a.phone_number , case when a.next_date != trunc(sysdate) then a.status||' from '|| a.start_date ||' to '||to_char(a.next_date - 1) else a.status||' from '||a.start_date ||' to date' end as status_text from a where a.store_name in ( select store_name from a where phone_number != next_number) order by a.store_name, a.start_date / And here's its output: SQL> r 1 with a as ( select store_name ... 22 order by a.store_name, a.start_date 23 / STORE_NAME PHONE_NUMBER STATUS_TEXT -------------------- ------------ -------------------------------- abc 1234 open from 30-DEC-11 to 03-JAN-12 abc 3433 close from 04-JAN-12 to date 2 rows selected. SQL> As for this remark: "so I also need to make sure that correlated query doesn't lock the table for whole lot of time" Doesn't matter in Oracle, because reads don't block other reads. Nor writes come to that.