Unclear, LAST_VALUE - Prior

Question

I have a table that looks like this:

Date Value 01/01/2010 03:59:00 324.44 01/02/2010 09:31:00 NULL 01/02/2010 09:32:00 NULL . . . 01/02/2010 11:42:00 NULL 

I want the first valid value to appear in all the following lines. This is what I did

 select date, nvl(value, LAST_VALUE(value IGNORE NULLS) over (order by value RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)) value from table 

It RANGE BETWEEN 3 PRECEDING AND CURRENT ROW no difference, but if I say RANGE BETWEEN 3 PRECEDING AND CURRENT ROW , it copies the data to all rows. I do not understand why this is happening. Can someone explain if I misunderstand how to use the previous ones?

Answer 1

Analytical functions still work on datasets. They do not process one row at a time, for this you will need PL / SQL or MODEL. PRECEDING refers to the last X lines, but before applying the analytic function.

These problems can be confusing in SQL because you need to build logic in the definition of a set instead of trying to pass data from one row to another. This is why I used CASE with LAST_VALUE in the previous answer.

---

Edit:

I added a simple dataset so that we can run the same query. VALUE1 seems to be working with me, am I missing something? Part of the problem with VALUE2 is that analytic ORDER BY uses VALUE instead of date.

 select id, the_date, value ,last_value(value ignore nulls) over (partition by id order by the_date) value1 ,nvl(value, LAST_VALUE(value IGNORE NULLS) over (order by value RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)) value2 from ( select 1 id, date '2011-01-01' the_date, 100 value from dual union all select 1 id, date '2011-01-02' the_date, null value from dual union all select 1 id, date '2011-01-03' the_date, null value from dual union all select 1 id, date '2011-01-04' the_date, null value from dual union all select 1 id, date '2011-01-05' the_date, 200 value from dual ) order by the_date; 

Results:

 ID THE_DATE VALUE VALUE1 VALUE2 1 1/1/2011 100 100 100 1 1/2/2011 100 1 1/3/2011 100 1 1/4/2011 100 1 1/5/2011 200 200 200 

Answer 2

It is possible to copy one row in time because I did it using java Logic and Sql query

  Statement sta; ResultSet rs,rslast; try{ 

// The code to create the connection and "con" is an object of the Connection class, so do not confuse it.

  sta = con.createStatement(); rs=sta.executeQuery("SELECT * FROM TABLE NAME"); rslast=sta.executeQuery("SELECT * FROM TABLENAME WHERE ID = (SELECT MAX(ID) FROM TABLENAME)"); rslast.next(); String digit =rslast.getString("ID"); System.out.print("ID"+rslast.getString("ID")); // it gives ID of the Last Record. 

Instead, using this method, u can also use ORDER by date in descending order.

Now I hope you make logic that only inserts the last record.