-1

I have data like below:

 ID          DATA          S_LEVEL 
1304819     M02-004           1
1304819     M01-003           2
1304819     M01-005           3
1304819     MT-M01-005        4
1304819     M01-004           5
1304819     M01-002           6
1304819     M01-009           7
1304819     M01-020           8
1304819     MT-3100-007A      9

I want output like below using connect by prior and level:

 ID          DATA        S_LEVEL     D_LEVEL
1304819     M02-004         1           9
1304819     M01-003         2           8
1304819     M01-005         3           7
1304819     MT-M01-005      4           6
1304819     M01-004         5           5
1304819     M01-002         6           4
1304819     M01-009         7           3
1304819     M01-020         8           2
1304819     MT-3100-007A    9           1
Vikrant
  • 4,920
  • 17
  • 48
  • 72
badkarma
  • 31
  • 1
  • 7

1 Answers1

0

use row_number() for D_level generate and apply order by

SELECT A.*, ROW_NUMBER () OVER (PARTITION BY PARTID ORDER BY MAX (S_LEVEL) DESC) AS D_LEVEL
  FROM (SELECT PARTID, DATA, S_LEVEL FROM TABLE 
       ) 
  GROUP BY PARTID,DATA,S_LEVEL ORDER BY PARTID,S_LEVEL,D_LEVEL DESC
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
  • it works if I have only one unique ID. But for multiple IDs above query is not working. – badkarma Apr 02 '19 at 20:02
  • Below query worked. SELECT A.*, ROW_NUMBER () OVER (PARTITION BY PARTID ORDER BY MAX (S_LEVEL) DESC) AS D_LEVEL FROM (SELECT PARTID, DATA, S_LEVEL FROM TABLE ) GROUP BY PARTID,DATA,S_LEVEL ORDER BY PARTID,S_LEVEL,D_LEVEL DESC – badkarma Apr 02 '19 at 20:50