Home » SQL & PL/SQL » SQL & PL/SQL » how to get previous row value?
| how to get previous row value? [message #608470] |
Thu, 20 February 2014 03:44  |
 |
pmreddy.mahi@gmail.com
Messages: 16
Registered: December 2011
|
Junior Member |
|
|
with t as(
SELECT '01-jan-2014' COL1, 'B' COL2, .001 COL3, .0006 COL4 FROM DUAL UNION ALL
SELECT '02-jan-2014' COL1, 'B' COL2, .002 COL3, .0007 COL4 FROM DUAL UNION ALL
SELECT '03-jan-2014' COL1, 'B' COL2, .003 COL3, .0008 COL4 FROM DUAL UNION ALL
SELECT '03-jan-2014' COL1, 'B' COL2, .002 COL3, .0009 COL4 FROM DUAL UNION ALL
SELECT '03-jan-2014' COL1, 'C' COL2, .004 COL3, null COL4 FROM DUAL UNION ALL
SELECT '04-jan-2014' COL1, 'C' COL2, .005 COL3, .0009 COL4 FROM DUAL UNION ALL
SELECT '04-jan-2014' COL1, 'C' COL2, .005 COL3, null COL4 FROM DUAL)
SELECT *
FROM T;
OUTPUT;
01-jan-2014 B 0.001 0.0006
02-jan-2014 B 0.002 0.0007
03-jan-2014 B 0.003 0.0008
03-jan-2014 B 0.002 0.0009
03-jan-2014 C 0.004
04-jan-2014 C 0.005 0.0009
04-jan-2014 C 0.005
Required Output should be:
01-jan-2014 B 0.001 0.0006
02-jan-2014 B 0.002 0.0007
03-jan-2014 B 0.003 0.0007
03-jan-2014 B 0.002 0.0007
03-jan-2014 C 0.004 0.0007
04-jan-2014 C 0.005 0.0009
04-jan-2014 C 0.005 0.0009
If we have multiple dates we need to get previous day value of COL4 and update that value to current day COL4 Rows..
|
|
|
|
|
|
|
|
| Re: how to get previous row value? [message #608474 is a reply to message #608473] |
Thu, 20 February 2014 04:18  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
And how are we supposed to work that out, seeing as there doesn't appear to be any data to indicaate that?
Or will all records have different times? If so your example should reflect that.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: how to get previous row value? [message #608486 is a reply to message #608474] |
Thu, 20 February 2014 04:46 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
cookiemonster wrote on Thu, 20 February 2014 10:18And how are we supposed to work that out, seeing as there doesn't appear to be any data to indicaate that?
Or will all records have different times? If so your example should reflect that.
|
|
|
|
| Re: how to get previous row value? [message #608491 is a reply to message #608486] |
Thu, 20 February 2014 05:29 |
 |
msol25
Messages: 396
Registered: June 2011
|
Senior Member |
|
|
hi,
Please find the query as per your requirement :
with t as(
SELECT '01-jan-2014' COL1, 'B' COL2, .001 COL3, .0006 COL4 FROM DUAL UNION ALL
SELECT '02-jan-2014' COL1, 'B' COL2, .002 COL3, .0007 COL4 FROM DUAL UNION ALL
SELECT '03-jan-2014' COL1, 'B' COL2, .003 COL3, .0008 COL4 FROM DUAL UNION ALL
SELECT '03-jan-2014' COL1, 'B' COL2, .002 COL3, .0009 COL4 FROM DUAL UNION ALL
SELECT '03-jan-2014' COL1, 'C' COL2, .004 COL3, null COL4 FROM DUAL UNION ALL
SELECT '04-jan-2014' COL1, 'C' COL2, .005 COL3, .0009 COL4 FROM DUAL UNION ALL
SELECT '04-jan-2014' COL1, 'C' COL2, .005 COL3, null COL4 FROM DUAL)
SELECT col1
,col2
,col3
,case when ROW_NUMBER() OVER ( ORDER BY col1) >= 2 then
( select col4
from t
where (col1,col3) in(
select case when ROW_NUMBER() OVER ( ORDER BY col1) = 2
then col1
end,col3
from t
)
)
else col4
end as a-----then col4 end end,*/
FROM T;
[Updated on: Thu, 20 February 2014 05:35] Report message to a moderator |
|
|
|
| Re: how to get previous row value? [message #608492 is a reply to message #608491] |
Thu, 20 February 2014 05:34 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
As I keep pointing out, order by col1 is not deterministic.
So that query is wrong. And no correct query can be given until the OP tells what to use to get a deterministic order.
|
|
|
|
|
|
|
|
| Re: how to get previous row value? [message #608495 is a reply to message #608494] |
Thu, 20 February 2014 05:45 |
|
msol25
Messages: 396
Registered: June 2011
|
Senior Member |
|
|
Yes Cookie,
I need to add col4 also.
Dear OP,
Please find query result :
with t as(
SELECT '01-jan-2014' COL1, 'B' COL2, .001 COL3, .0006 COL4 FROM DUAL UNION ALL
SELECT '02-jan-2014' COL1, 'B' COL2, .002 COL3, .0007 COL4 FROM DUAL UNION ALL
SELECT '03-jan-2014' COL1, 'B' COL2, .003 COL3, .0008 COL4 FROM DUAL UNION ALL
SELECT '03-jan-2014' COL1, 'B' COL2, .002 COL3, .0009 COL4 FROM DUAL UNION ALL
SELECT '03-jan-2014' COL1, 'C' COL2, .004 COL3, null COL4 FROM DUAL UNION ALL
SELECT '04-jan-2014' COL1, 'C' COL2, .005 COL3, .0009 COL4 FROM DUAL UNION ALL
SELECT '04-jan-2014' COL1, 'C' COL2, .005 COL3, null COL4 FROM DUAL)
SELECT col1
,col2
,col3
,case when ROW_NUMBER() OVER ( ORDER BY col1) >= 2 then
( select col4
from t
where (col1,col3,col4) in(
select case when ROW_NUMBER() OVER ( ORDER BY col1) = 2
then col1
end,col3,col4
from t
)
)
else col4
end as a-----then col4 end end,*/
FROM T;
|
|
|
|
|
|
| Re: how to get previous row value? [message #608498 is a reply to message #608496] |
Thu, 20 February 2014 06:02 |
|
msol25
Messages: 396
Registered: June 2011
|
Senior Member |
|
|
Thanks Michel sir,
I used only col1 and col4:
Dear OP,
Please find the query result :
with t as(
SELECT '01-jan-2014' COL1, 'B' COL2, .001 COL3, .0006 COL4 FROM DUAL UNION ALL
SELECT '02-jan-2014' COL1, 'B' COL2, .002 COL3, .0007 COL4 FROM DUAL UNION ALL
SELECT '03-jan-2014' COL1, 'B' COL2, .003 COL3, .0008 COL4 FROM DUAL UNION ALL
SELECT '03-jan-2014' COL1, 'B' COL2, .002 COL3, .0009 COL4 FROM DUAL UNION ALL
SELECT '03-jan-2014' COL1, 'C' COL2, .004 COL3, null COL4 FROM DUAL UNION ALL
SELECT '04-jan-2014' COL1, 'C' COL2, .005 COL3, .0009 COL4 FROM DUAL UNION ALL
SELECT '04-jan-2014' COL1, 'C' COL2, .005 COL3, null COL4 FROM DUAL)
SELECT col1
---,col2
--- ,col3
,case when ROW_NUMBER() OVER ( ORDER BY col1) >= 2 then
( select col4
from t
where (col1,col4) in(
select case when ROW_NUMBER() OVER ( ORDER BY col1) = 2
then col1
end,
---- col3,
col4
from t
)
)
else col4
end as a-----then col4 end end,*/
FROM T;
|
|
|
|
|
|
|
|
| Re: how to get previous row value? [message #608520 is a reply to message #608492] |
Thu, 20 February 2014 08:06 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
This is the output your query gives, with just the original rows supplied by the OP:
COL1 A
----------- ----------
01-jan-2014 0.0006
02-jan-2014 0.0007
03-jan-2014 0.0007
03-jan-2014 0.0007
03-jan-2014 0.0007
04-jan-2014 0.0007
04-jan-2014 0.0007
It does not match the OPs expected output, 4th Jan is wrong.
But before you try and correct this query again I'll repeat myself one last time:
cookiemonster wrote on Thu, 20 February 2014 11:34As I keep pointing out, order by col1 is not deterministic.
So that query is wrong. And no correct query can be given until the OP tells what to use to get a deterministic order.
You seem determined to ignore that part in bold above. Now unless you're psychic (and if you are I'd like this weeks lottery numbers please) you, like the rest of us, are in position to provide a solution to the OPs problem.
|
|
|
|
|
|
| Re: how to get previous row value? [message #608524 is a reply to message #608523] |
Thu, 20 February 2014 08:14 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
And just to spell out what your current query is doing:
It's replacing col4 of every row after the first (ordered by col1) with the value of col4 from the 2nd row (ordered by col1).
It's not even trying to check which rows are for the previous day at all.
|
|
|
|
| Re: how to get previous row value? [message #608527 is a reply to message #608470] |
Thu, 20 February 2014 08:24 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Instead of pointing ambiguousness of some code, what about trying to get exact rules for the result set and implement them?
Quote:If we have multiple dates we need to get previous day value of COL4 and update that value to current day COL4 Rows..
I understand it as: when there are multiple rows for given COL1, fill COL4 with the value of COL4 from the closest previous (single) row (where is only one row with given COL1). Assume sorting by COL1.
with T as (<see first post>)
-- determine count of rows with the same COL1
, get_cnts as ( select t.*, count(*) over (partition by col1) nr_rows
from T )
-- take the value from the latest row having that count equal to 1
, get_rows as ( select get_cnts.*,
last_value(case when nr_rows = 1 then col4 end ignore nulls)
over ( order by col1 ) prev_nondup_col4
from get_cnts )
select * from get_rows;
Of course it would be nice if pmreddy.mahi would be able to clearly state these rules.
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 15:17:01 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
