Home » SQL & PL/SQL » SQL & PL/SQL » retrieve first changed row
| retrieve first changed row [message #648015] |
Mon, 15 February 2016 00:52  |
 |
suji6281
Messages: 134
Registered: September 2014
|
Senior Member |
|
|
we need to fetch the row when comp_rate got changed.
As per attached insert scripts we should get recently changed comp_rate row, i.e 4th row. Please let me know the query.
CREATE TABLE customers
( customer_id number(10) NOT NULL,
eff_dt varchar2(50) NOT NULL,
comp_rate number(5) NOT NULL
)
INSERT INTO customers values (1000, '2012-01-01', 100)
INSERT INTO customers values (1000, '2013-01-01', 200)
INSERT INTO customers values (1000, '2014-01-01', 300)
INSERT INTO customers values (1000, '2015-01-01', 400)
INSERT INTO customers values (1000, '2015-03-01', 400)
[mod-edit: contents of text file inserted into message body by bb]
[Updated on: Mon, 15 February 2016 15:43] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: retrieve first changed row [message #648019 is a reply to message #648018] |
Mon, 15 February 2016 01:22  |
|
suji6281
Messages: 134
Registered: September 2014
|
Senior Member |
|
|
Hi Michol,
Please consider below table.
customer_id eff_dt comp_rate
1000 2012-01-01 100
1000 2013-01-01 200
1000 2014-01-01 300
1000 2015-01-01 400
1000 2015-03-01 400
1000 2015-04-01 400
we should retrieve the recently changed comp_rate field as per the eff_dt field. In the above example the output should be 4th row (i.e 2015-01-01).
Please help me with sql query. Thank You.
[mod-edit: code tags added by bb]
[Updated on: Mon, 15 February 2016 15:45] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: retrieve first changed row [message #648027 is a reply to message #648019] |
Mon, 15 February 2016 03:14 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
suji6281 wrote on Mon, 15 February 2016 07:22Hi Michol,
Please consider below table.
customer_id eff_dt comp_rate
1000 2012-01-01 100
1000 2013-01-01 200
1000 2014-01-01 300
1000 2015-01-01 400
1000 2015-03-01 400
1000 2015-04-01 400
we should retrieve the recently changed comp_rate field as per the eff_dt field. In the above example the output should be 4th row (i.e 2015-01-01).
Please help me with sql query. Thank You.
Unless you have a typo there, or I have misread, that is not logical. Why 2015-01-01?
Ed: Seems I might have misread. You want the first date it changed? In that case lag/lead will do it, yes.
[Updated on: Mon, 15 February 2016 03:17] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: retrieve first changed row [message #648183 is a reply to message #648181] |
Thu, 18 February 2016 03:31 |
 |
varlamovvp
Messages: 13
Registered: February 2016
Location: Russia
|
Junior Member |
|
|
Michel Cadot!
Actually we are solving different problems.
Nobody knows what the author does exactly mean.
Not all of the conditions are identified.
We are searching for a spherical horse in a vacuum.
Hi suji6281!
Please consider below table.
c_id eff_dt comp_rate
1000, '2012-01-01', 100
1000, '2013-01-01', 200
2000, '2013-02-01', 200
2000, '2013-03-01', 200
1000, '2013-04-01', 200
5000, '2014-01-01', 400
5000, '2014-02-01', 400
5000, '2014-03-01', 400
Please show us the correct output of query in this case.
|
|
|
|
| Re: retrieve first changed row [message #648186 is a reply to message #648183] |
Thu, 18 February 2016 05:15 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
No the problem is clear, even if not fully specified.
The table customers has 3 columns:
- customer_id, so there can be several customers
- eff_dt, the date of the data
- comp_rate, the data itself
With no more information abut the data, we can say it can have any value in its domain range but what we know is, for each customer, it can change. It can increase, it can decrease, it can stay the same.
The test case is not complete but you can easily add the rows you want.
Your data does not show the case where it decreases.
For me, there is only one row to return for your test case: the second one as it is the lone row where the data change for a customer.
|
|
|
|
|
|
|
|
|
|
| Re: retrieve first changed row [message #648244 is a reply to message #648236] |
Fri, 19 February 2016 03:03 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Nope, try again. 
Here's a new test case (I changed the type of eff_dt to a real DATE):
drop TABLE customers;
CREATE TABLE customers
( customer_id number(10) NOT NULL,
eff_dt date NOT NULL,
comp_rate number(5) NOT NULL
)
/
INSERT INTO customers values (1000, to_date('2012-01-01','YYYY-MM-DD'), 100);
INSERT INTO customers values (1000, to_date('2013-01-01','YYYY-MM-DD'), 200);
INSERT INTO customers values (1000, to_date('2014-01-01','YYYY-MM-DD'), 300);
INSERT INTO customers values (1000, to_date('2015-01-01','YYYY-MM-DD'), 400);
INSERT INTO customers values (1000, to_date('2015-03-01','YYYY-MM-DD'), 400);
INSERT INTO customers values (2000, to_date('2013-06-01','YYYY-MM-DD'), 200);
INSERT INTO customers values (2000, to_date('2013-09-01','YYYY-MM-DD'), 200);
INSERT INTO customers values (1000, to_date('2012-01-01','YYYY-MM-DD'), 100);
INSERT INTO customers values (3000, to_date('2013-01-01','YYYY-MM-DD'), 200);
INSERT INTO customers values (3000, to_date('2014-01-01','YYYY-MM-DD'), 300);
INSERT INTO customers values (3000, to_date('2015-01-01','YYYY-MM-DD'), 200);
INSERT INTO customers values (3000, to_date('2015-03-01','YYYY-MM-DD'), 200);
commit;
select * from customers order by customer_id, eff_dt;
SQL> select * from customers order by customer_id, eff_dt;
CUSTOMER_ID EFF_DT COMP_RATE
----------- ----------- ----------
1000 01-JAN-2012 100
1000 01-JAN-2012 100
1000 01-JAN-2013 200
1000 01-JAN-2014 300
1000 01-JAN-2015 400
1000 01-MAR-2015 400
2000 01-JUN-2013 200
2000 01-SEP-2013 200
3000 01-JAN-2013 200
3000 01-JAN-2014 300
3000 01-JAN-2015 200
3000 01-MAR-2015 200
The result should be, assuming if there is no change for a customer there is no rows returned:
CUSTOMER_ID EFF_DT COMP_RATE
----------- ----------- ----------
1000 01-JAN-2015 400
3000 01-JAN-2015 200
or, assuming if there is no change for a customer you have to return his first row:
CUSTOMER_ID EFF_DT COMP_RATE
----------- ----------- ----------
1000 01-JAN-2015 400
2000 01-JUN-2013 200
3000 01-JAN-2015 200
|
|
|
|
|
|
| Re: retrieve first changed row [message #648575 is a reply to message #648556] |
Thu, 25 February 2016 14:13 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Not that efficient. All we need is analytic functions - no need to group by. Compare execution plans:
with t as (
select c.*,
lag(comp_rate,1,comp_rate)
over(
partition by customer_id
order by eff_dt
) prev_comp_rate,
max(comp_rate)
over(
partition by customer_id
order by eff_dt
rows between 1 following and unbounded following
) max_rest_of_comp_rate,
min(comp_rate)
over(
partition by customer_id
order by eff_dt
rows between 1 following and unbounded following
) min_rest_of_comp_rate
from customers c
)
select customer_id,
eff_dt,
comp_rate
from t
where comp_rate != prev_comp_rate
and comp_rate = nvl(max_rest_of_comp_rate,comp_rate)
and comp_rate = nvl(min_rest_of_comp_rate,comp_rate)
/
CUSTOMER_ID EFF_DT COMP_RATE
----------- --------- ----------
1000 01-JAN-15 400
3000 01-JAN-15 200
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID 4tunsbstpxcdr, child number 0
-------------------------------------
with t as ( select c.*,
lag(comp_rate,1,comp_rate) over(
partition by customer_id order by
eff_dt ) prev_comp_rate,
max(comp_rate) over(
partition by customer_id order by eff_dt
rows between 1 following and unbounded following
) max_rest_of_comp_rate,
min(comp_rate) over(
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
partition by customer_id order by eff_dt
rows between 1 following and unbounded following
) min_rest_of_comp_rate from
customers c ) select customer_id, eff_dt,
comp_rate from t where comp_rate != prev_comp_rate and
comp_rate = nvl(max_rest_of_comp_rate,comp_ra
Plan hash value: 3113798517
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
|* 1 | VIEW | | 12 | 888 | 4 (25)| 00:00:01 |
| 2 | WINDOW SORT | | 12 | 420 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| CUSTOMERS | 12 | 420 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("COMP_RATE"<>"PREV_COMP_RATE" AND
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
"COMP_RATE"=NVL("MAX_REST_OF_COMP_RATE","COMP_RATE") AND
"COMP_RATE"=NVL("MIN_REST_OF_COMP_RATE","COMP_RATE")))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
40 rows selected.
SELECT
customer_id,
max(eff_dt) eff_dt,
max(comp_rate) KEEP (dense_rank last order by eff_dt) comp_rate
FROM
(
select
c.*,
LAG(comp_rate) OVER (partition by customer_id ORDER BY eff_dt) lag_comp_rate
from customers c
)
WHERE comp_rate<>lag_comp_rate
GROUP BY customer_id
/
CUSTOMER_ID EFF_DT COMP_RATE
----------- --------- ----------
1000 01-JAN-15 400
3000 01-JAN-15 200
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID gnzju1v4szfkh, child number 0
-------------------------------------
SELECT customer_id, max(eff_dt) eff_dt, max(comp_rate) KEEP
(dense_rank last order by eff_dt) comp_rate FROM ( select c.*,
LAG(comp_rate) OVER (partition by customer_id ORDER BY eff_dt)
lag_comp_rate from customers c ) WHERE comp_rate<>lag_comp_rate GROUP
BY customer_id
Plan hash value: 475449762
----------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT GROUP BY NOSORT| | 12 | 576 | 4 (25)| 00:00:01 |
|* 2 | VIEW | | 12 | 576 | 4 (25)| 00:00:01 |
| 3 | WINDOW SORT | | 12 | 420 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| CUSTOMERS | 12 | 420 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
2 - filter("COMP_RATE"<>"LAG_COMP_RATE")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
29 rows selected.
SY.
[Updated on: Thu, 25 February 2016 14:17] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 10:21:50 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
