B
B
bora_bora2015-11-05 18:59:47
PostgreSQL
bora_bora, 2015-11-05 18:59:47

Is it possible to solve this problem using Window Functions?

Help me to understand.
There is a table that contains data like:

code	 | 		mod_date
-------------------------------
  1	 |	2015-10-01 10:00:00
  1	 |	2015-10-13 22:01:00
  2 	 |	2015-10-13 23:10:00
  2	 |	2015-10-29 14:00:00
  1	 |	2015-10-30 11:01:00
  1	 |	2015-11-01 06:50:00
  3	 |	2015-11-03 10:00:00

You need to write a query that will return:
code	 | mod_date                     |	n_date
-------------------------------------------------------
  1	 |	2015-10-01 10:00:00	|	2015-10-01 10:00:00
  1	 |	2015-10-13 22:01:00	|	2015-10-01 10:00:00
  2 	 |	2015-10-13 23:10:00	|	2015-10-13 23:10:00
  2	 |	2015-10-29 14:00:00	|	2015-10-13 23:10:00
  1	 |	2015-10-30 11:01:00	|	2015-10-30 11:01:00
  1	 |	2015-11-01 06:50:00	|	2015-10-30 11:01:00
  3	 |	2015-11-03 10:00:00	|	2015-11-03 10:00:00

The first wrong solution that came to mind is:
first_value(mod_date) OVER(PARTITION BY code_org ORDER BY mod_date)
But it will not work if you need to take into account breaks in the code.
That is, when we have a break in code, for example, in code = 1 and code = 2 appears, then the value of mod_date should no longer be taken as "2015-10-01 10:00:00", but "2015-10-30" 11:01:00".

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question