SQL recursive

1) This is my table X

select to_char(column1, 'DDMMYYYY HH24:MI:SS') COLUMN1,
COLUMN2, COLUMN3 from x;

COLUMN1           COLUMN2    COL
----------------- ---------- ---
31012008 10:25:27 JOSE       A
31012008 10:25:37 BRYAN      A
31012008 10:25:46 CARLOS     A
31012008 10:25:54 MARIA      B
31012008 10:26:08 BRYAN      B
31012008 10:26:16 JOSE       B

6 rows selected.

2) I need to show
COL          SEQ COLUMN2    (show here previous row COLUMN2)
--- ------------ ----------
A              1 JOSE                      NULL
A              2 BRYAN                  JOSE
A              3 CARLOS                BRYAN
B              1 MARIA                    NULL
B              2 BRYAN                  MARIA
B              3 JOSE                      JOSE

3) (sujith80) provides this sql
SELECT COLUMN3, ROW_NUMBER() OVER(PARTITION BY COLUMN3 ORDER BY COLUMN1) SEQ, COLUMN2
FROM X

4) I still need the third column, which is the person who is transferring the document.

Could you help me? Thank in advance.
I am posting 50 points, I will double it with the solution.
LVL 5
ZopiloteAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jinesh KamdarCommented:
Here's a solution in PL/SQL (untested).
SET SERVEROUT ON
DECLARE
seq_no    NUMBER         := 0;
v_col     x.col%TYPE     := NULL;
v_volumn2 x.column2%TYPE := NULL;
BEGIN
DBMS_OUTPUT.PUT_LINE('COL, SEQ, COLUMN2, PREVIOUS');
DBMS_OUTPUT.PUT_LINE('---------------------------');
FOR i in (SELECT * FROM x ORDER BY col, column1) LOOP
    IF seq = 0 OR v_col <> i.col THEN
       seq := 1;
    END IF;
    DBMS_OUTPUT.PUTLINE(col || ',' seq || ',' || i.column2 || ',' || v_column2);
    v_col     := i.col;
    v_column2 := i.column2;
    seq := seq + 1;
END LOOP;
END;

Open in new window

0
ZopiloteAuthor Commented:
thank you, but I need it in SQL (I am sorry if by mistake I publish this question in the wrong place)
0
Jinesh KamdarCommented:
You mean SQL server or in a single SQL query? For the former, you should post it in the MS SQL Server zone. For the latter, I'm not sure if that's possible in a single query unless 1 of the analytics champs out here comes up with a winner!
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ZopiloteAuthor Commented:
SQL query.
Yes, it is possible.
Thanks!
0
sventhanCommented:
SELECT COLUMN3, ROW_NUMBER() OVER(PARTITION BY COLUMN3 ORDER BY COLUMN1) SEQ, COLUMN2,LAG(COLUMN2, 1) OVER(PARTITION BY COLUMN3 ORDER BY COLUMN3 ASC) prev
FROM X
0
sventhanCommented:
CREATE TABLE X (COL VARCHAR2(1), SEQ NUMBER, COL2 VARCHAR2(10));

Insert into COMP_USR.X
   (COL, SEQ, COL2)
 Values
   ('A', 1, 'JOSE');
Insert into COMP_USR.X
   (COL, SEQ, COL2)
 Values
   ('B', 3, 'JOSE');
Insert into COMP_USR.X
   (COL, SEQ, COL2)
 Values
   ('A', 2, 'BRYAN');
Insert into COMP_USR.X
   (COL, SEQ, COL2)
 Values
   ('A', 3, 'CARLOS');
Insert into COMP_USR.X
   (COL, SEQ, COL2)
 Values
   ('B', 1, 'MARIA');
Insert into COMP_USR.X
   (COL, SEQ, COL2)
 Values
   ('B', 2, 'BRYAN');
COMMIT;


SELECT COL, ROW_NUMBER() OVER(PARTITION BY COL ORDER BY COL) SEQ, COL2,LAG(col2, 1) OVER(PARTITION BY COL ORDER BY col ASC) prev
FROM X
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jinesh KamdarCommented:
Good one sventhan. I guess its high time for me to learn analytics now!
0
ZopiloteAuthor Commented:
sventhan: THANK YOU SO MUCH!!! you saved my life Z:D
0
sventhanCommented:
Glad it works.
Thanks for the comments.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.