Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
0


CREATE TABLE t
(  
  a_id       NUMBER not null,
  t_id       NUMBER,
  c_name       VARCHAR2(100),
  d_order     NUMBER default 0,
  CONSTRAINT t_pk PRIMARY KEY (a_id, t_id)
);

INSERT INTO t
WITH cte1 (a_id, t_id, c_name, d_order) AS 
(
  SELECT 675150, 770650, 'name', 0 FROM dual UNION
  SELECT 675150, 780700, 'name', 0 FROM dual UNION
  SELECT 675150, 780701, 'name', 0 FROM dual UNION
  SELECT 675180, 770550, 'name', 0 FROM dual UNION
  SELECT 675180, 780800, 'name', 0 FROM dual UNION
  SELECT 675180, 780801, 'name', 0 FROM dual
)
SELECT * FROM cte1;

6 rows affected

SELECT * FROM t;


A_ID
T_ID
C_NAME
D_ORDER


675150
770650
name
0

675150
780700
name
0

675150
780701
name
0

675180
770550
name
0

675180
780800
name
0

675180
780801
name
0


SELECT 
  t.a_id, 
  t.t_id, 
  t.d_order, 
ROW_NUMBER() OVER (PARTITION BY t.a_id 
                            ORDER BY t.a_id, t.t_id DESC) AS rn
FROM t;


A_ID
T_ID
D_ORDER
RN


675150
780701
0
1

675150
780700
0
2

675150
770650
0
3

675180
780801
0
1

675180
780800
0
2

675180
770550
0
3

A_ID	T_ID	C_NAME
675150	770650	name
675150	780700	name
675150	780701	name
675180	770550	name
675180	780800	name
675180	780801	name