Jump to content
Ciccio

Problems with the Oracle LAST_VALUE analytic function

Recommended Posts

I have a table like this:

 ID |  Val   |  Ord
----------------------
 1  |  1000  |   1
 1  |  1000  |   2
 1  |  2000  |   3
 1  |  2000  |   4
 1  |  2000  |   5
 1  |  1000  |   6
 1  |  1000  |   7

I want to make a SELECT that will return

Sample output:

 ID |  Val   |  Ord
----------------------
 1  |  1000  |   2
 1  |  2000  |   5
 1  |  1000  |   7
 
 
the output I can produce is

 ID |  Val   |  Ord
----------------------
 1  |  1000  |   7
 1  |  2000  |   5

 I would like to use the last_value function but I have problems when the value assigned to the same key repeats alternated with a different value

How can I build this query?

Thanks for your help
Franz

Share this post


Link to post
Share on other sites

create table TEST_LAST_VALUE (
ID NUMBER(2),
VAL NUMBER(5),
ORD NUMBER(2)
);

INSERT INTO TEST_LAST_VALUE (id, val, ord) VALUES (1, 1000, 1);
INSERT INTO TEST_LAST_VALUE (id, val, ord) VALUES (1, 1000, 2);
INSERT INTO TEST_LAST_VALUE (id, val, ord) VALUES (1, 2000, 3);
INSERT INTO TEST_LAST_VALUE (id, val, ord) VALUES (1, 2000, 4);
INSERT INTO TEST_LAST_VALUE (id, val, ord) VALUES (1, 2000, 5);
INSERT INTO TEST_LAST_VALUE (id, val, ord) VALUES (1, 1000, 6);
INSERT INTO TEST_LAST_VALUE (id, val, ord) VALUES (1, 1000, 7);
COMMIT;

SELECT K.ID, K.VAL, K.ORD
FROM (
   SELECT T.*, LAST_VALUE(ORD) OVER (PARTITION BY ID, VAL
                                    ORDER BY ORD ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS LV
   FROM TEST_LAST_VALUE T
) K
WHERE K.ORD = K.LV
ORDER BY 1,2

 

Share this post


Link to post
Share on other sites

does ord reset for a new id? so if the next row in your data set were ID = 2, or would ord be 8?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×