CODEWITHSUNDEEP
sqloracle-database
WellingtonD
WellingtonD

Reputation: 165

oracle identify every change of a column

I have the following table:

CC   | BEGIN               | END                 | ORDER

1215 | 20.03.2015 14:59:21 | 23.03.2015 13:47:19 | 1
1310 | 23.03.2015 13:47:19 | 27.03.2015 19:19:23 | 2
1105 | 27.03.2015 19:19:23 | 03.04.2015 18:10:22 | 3
1205 | 03.04.2015 18:10:22 | 04.04.2015 17:48:51 | 4
4650 | 04.04.2015 17:48:51 | 05.04.2015 13:28:20 | 5
1215 | 05.04.2015 13:28:20 | 10.04.2015 11:00:01 | 6
1310 | 12.04.2015 15:10:52 | 20.04.2015 09:44:39 | 7
1220 | 20.04.2015 09:44:39 | 22.04.2015 18:10:13 | 8
1220 | 23.04.2015 10:46:25 | 23.04.2015 10:49:08 | 9
1215 | 23.04.2015 10:49:08 | 23.04.2015 11:00:14 | 10
1215 | 23.04.2015 11:00:14 | 23.04.2015 11:47:45 | 11
1215 | 23.04.2015 11:47:45 | 29.04.2015 04:33:27 | 12
1215 | 29.04.2015 04:33:27 | 01.05.2015 16:46:53 | 13
2880 | 01.05.2015 16:46:53 | 06.05.2015 15:55:13 | 14
1210 | 06.05.2015 15:55:13 | 08.05.2015 16:35:40 | 15
1210 | 08.05.2015 16:35:40 | 15.05.2015 18:10:00 | 16
1205 | 17.05.2015 14:19:01 | 21.05.2015 15:29:08 | 17
1205 | 21.05.2015 15:29:08 | 24.05.2015 16:59:14 | 18
1205 | 24.05.2015 16:59:14 | 27.05.2015 14:33:59 | 19
1205 | 27.05.2015 14:33:59 | 01.06.2015 12:42:45 | 20
1205 | 27.05.2015 14:33:59 | 01.06.2015 12:42:45 | 20

I'm looking for a way to put an unique number every time the column CC changes. E.g.:

CC   | BEGIN               | END                 | ORDER  | COLUMN I WISH FILL

1215 | 20.03.2015 14:59:21 | 23.03.2015 13:47:19 | 1      | 1 
1310 | 23.03.2015 13:47:19 | 27.03.2015 19:19:23 | 2      | 2
1105 | 27.03.2015 19:19:23 | 03.04.2015 18:10:22 | 3      | 3
1205 | 03.04.2015 18:10:22 | 04.04.2015 17:48:51 | 4      | 4
4650 | 04.04.2015 17:48:51 | 05.04.2015 13:28:20 | 5      | 5
1215 | 05.04.2015 13:28:20 | 10.04.2015 11:00:01 | 6      | 6
1310 | 12.04.2015 15:10:52 | 20.04.2015 09:44:39 | 7      | 7
1220 | 20.04.2015 09:44:39 | 22.04.2015 18:10:13 | 8      | 8
1220 | 23.04.2015 10:46:25 | 23.04.2015 10:49:08 | 9      | 8
1215 | 23.04.2015 10:49:08 | 23.04.2015 11:00:14 | 10     | 9
1215 | 23.04.2015 11:00:14 | 23.04.2015 11:47:45 | 11     | 9
1215 | 23.04.2015 11:47:45 | 29.04.2015 04:33:27 | 12     | 9
1215 | 29.04.2015 04:33:27 | 01.05.2015 16:46:53 | 13     | 9
2880 | 01.05.2015 16:46:53 | 06.05.2015 15:55:13 | 14     | 10
1210 | 06.05.2015 15:55:13 | 08.05.2015 16:35:40 | 15     | 11
1210 | 08.05.2015 16:35:40 | 15.05.2015 18:10:00 | 16     | 11
1205 | 17.05.2015 14:19:01 | 21.05.2015 15:29:08 | 17     | 12
1205 | 21.05.2015 15:29:08 | 24.05.2015 16:59:14 | 18     | 12
1205 | 24.05.2015 16:59:14 | 27.05.2015 14:33:59 | 19     | 12
1205 | 27.05.2015 14:33:59 | 01.06.2015 12:42:45 | 20     | 12

I've tried using the LAG() and LEAD() function, it works fine only when there are two repetitions of the column CC

Upvotes: 1

Views: 65

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

This should work:

SELECT CC, "BEGIN", "END", "ORDER",
       SUM(flag) OVER (ORDER BY "ORDER")  AS new_column
FROM (
  SELECT CC, "BEGIN", "END", "ORDER",
         CASE WHEN LAG(CC) OVER (ORDER BY "ORDER") = CC THEN 0 ELSE 1 END AS flag
  FROM mytable) AS t

Upvotes: 2

smnbbrv
smnbbrv

Reputation: 24531

This is what is usually done with a RANK / DENSE_RANK functions.

See the DENSE_RANK documentation, you would need to use it somehow like that:

select ..., dense_rank() over (order by cc) column_i_wish, ...

Upvotes: 1

Related Questions