I need your help about this matter;
Fiedl1_Id Fiedl1 Fiedl2_Id Fiedl2
2470 199T 2348 949T
2470 199T 2349 699T
2470 199T 2356 649T
2470 199T 2379 399T
2470 199T 2383 299T
2470 199T 2470 199T
I want to to implement into my sql query a code to add to this output of a query, a new field Depth, its principle is as follows:
Fiedl1_Id Fiedl1 Fiedl2_Id Fiedl2 Depth
2470 199T 2348 949T 1
2470 199T 2348 949T 2
2470 199T 2348 949T 3
2470 199T 2348 949T 4
2470 199T 2348 949T 5
2470 199T 2349 699T 1
2470 199T 2349 699T 2
2470 199T 2349 699T 3
2470 199T 2349 699T 4
2470 199T 2356 649T 1
2470 199T 2356 649T 2
2470 199T 2356 649T 3
2470 199T 2379 399T 1
2470 199T 2379 399T 2
2470 199T 2383 299T 1
2470 199T 2470 199T 0
I had 6 recordings including a record that has the Fiedl1_Id = Fiedl2_Id ( 2470) this one must have a Depth = 0, for others; have 5 depths for the first value of Fiedl2(949T), 4 depths for (699T) and so on in descending order.
This gives you the desired output for the given input:
WITH t (fiedl1_id,fiedl1,fiedl2_id,fiedl2)
AS
(
SELECT '2470','199T','2348','949T' FROM DUAL UNION ALL
SELECT '2470','199T','2349','699T' FROM DUAL UNION ALL
SELECT '2470','199T','2356','649T' FROM DUAL UNION ALL
SELECT '2470','199T','2379','399T' FROM DUAL UNION ALL
SELECT '2470','199T','2383','299T' FROM DUAL UNION ALL
SELECT '2470','199T','2470','199T' FROM DUAL
), t_rn (fiedl1_id,fiedl1,fiedl2_id,fiedl2,rn)
AS
(
SELECT fiedl1_id,fiedl1,fiedl2_id,fiedl2,ROW_NUMBER() OVER(ORDER BY 1) FROM t ORDER BY fiedl2 ASC
), t_depth (fiedl1_id,fiedl1,fiedl2_id,fiedl2,fdepth)
AS
(
SELECT fiedl1_id,fiedl1,fiedl2_id,fiedl2,rn - 1 FROM t_rn
UNION ALL
SELECT fiedl1_id,fiedl1,fiedl2_id,fiedl2,fdepth - 1 FROM t_depth WHERE fdepth - 1 > 0
)
SELECT * FROM t_depth order by fiedl2 DESC, fdepth ASC;
FIED FIED FIED FIED FDEPTH
---- ---- ---- ---- ----------
2470 199T 2348 949T 1
2470 199T 2348 949T 2
2470 199T 2348 949T 3
2470 199T 2348 949T 4
2470 199T 2348 949T 5
2470 199T 2349 699T 1
2470 199T 2349 699T 2
2470 199T 2349 699T 3
2470 199T 2349 699T 4
2470 199T 2356 649T 1
2470 199T 2356 649T 2
2470 199T 2356 649T 3
2470 199T 2379 399T 1
2470 199T 2379 399T 2
2470 199T 2383 299T 1
2470 199T 2470 199T 0