Jump to content

Ciccio

Members
  • Content count

    3
  • Joined

  • Last visited

Community Reputation

0 Neutral

About Ciccio

  • Rank
    Newbie
  1. ID is a broken key, I need reset for new id
  2. 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
  3. 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
×