1

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

Improve this question

2 Answers 2

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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, ...
Improve this answer

1 Comment

This won't work because order is specified by "ORDER" field, not "CC".

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.