[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 589
  • Last Modified:

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.
0
Zopilote
Asked:
Zopilote
  • 3
  • 3
  • 3
1 Solution
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now