I have following query now:

select ofd.document_id, ofd.last_modified_time , xmlt.*

from orbeon_form_data ofd

cross join lateral
XMLTABLE
(
 '/form/section-uczestnicy/section-uczestnicy-iteration/section-uczestnik/section-dane-uczestnika'
 PASSING by ref "xml" 
 columns
  "control-create" text path '/form/teryt-metadata/control-created',
  "app-signature" text path '/form/section-informacje-o-projekcie/control-sygnatura-nawa',
  "control-sygnatura" text path '/form/section-informacje-o-projekcie/control-numer-projektu',
  "control-inst" text path '/form/section-instytucja/section-instytucjaj-dane-podstawowe/control-inst-nazwa',
  "control-imie" text PATH 'control-imie',
  "control-nazwisko" text PATH 'control-nazwisko'
) xmlt
where ofd.form='Monitorowanie_Uczestnikow' and ofd.form_version=1 and 
ofd.document_id='0ea8329ff9efe8a3052264fa494243b6323ae433'
order by ofd.document_id, ofd.last_modified_time desc;

this query gives me following result:

document_id                 last_modified_time  date_create     app-signature   control-signatura   control-inst    control-imie    control-nazwisko
_________________________________________________________________________________________________________________________________________________________________________________________       
0ea8329ff9efe8a3052264fa494243b6323ae433    2018-12-13 09:12:24.179 2018-12-13T08:12:23Z            werrwe          wwww        Adam        Wybifghfghk
0ea8329ff9efe8a3052264fa494243b6323ae433    2018-12-13 09:12:24.179 2018-12-13T08:12:23Z            werrwe          www     Adam        Wybierak
0ea8329ff9efe8a3052264fa494243b6323ae433    2018-12-13 09:11:25.740 2018-12-13T08:11:20Z            werrwe          wwww        Adam        Wybierak
0ea8329ff9efe8a3052264fa494243b6323ae433    2018-12-13 09:11:16.874 2018-12-13T08:11:11Z            werrwe          wwww        Adam        Wybierak
0ea8329ff9efe8a3052264fa494243b6323ae434    2018-12-13 09:12:24.179 2018-12-13T08:12:23Z            werrwe          www     Adam        Wybierak
0ea8329ff9efe8a3052264fa494243b6323ae434    2018-12-13 09:11:25.740 2018-12-13T08:11:20Z            werrwe          wwww        Adam        sdfsdf

Now I want to select from this results records grouped by document_id with latest last_modification time, so I want to get only record numer 1,2 (casuse it have same document_id and latest last_modified_time) record 5 (latest last_modification_time for document_id 0ea8329ff9efe8a3052264fa494243b6323ae434 )

I try to do it by grouping by document_id and max(last_modified_time) also with distinct on (document_id) sort by last_modified_time (but it gives me only one record per document_id - in my case I must select 2 records with same 0ea8329ff9efe8a3052264fa494243b6323ae433 document_id cause it have last_modified_time 2018-12-13 09:12:24.17 )

CodePudding user response：

Try something like :

SELECT *
FROM (SELECT *, CASE WHEN last_modified_time = max(last_modified_time) OVER (PARTITION BY document_id) THEN TRUE ELSE FALSE END AS time_max
        FROM your_table) AS t
WHERE t.time_max

see dbfiddle