我現在有以下查詢：
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;
這個查詢給了我以下結果：
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現在我想從這個按document_id分組的結果記錄中選擇最新的last_modification時間，所以我只想得到記錄號1,2（因為它有相同的document_id和最新的last_modified_time） 記錄5（最新的last_modification_time for document_id 0ea8329ff9efe8a3052264fa494243b6323ae434）
我嘗試通過按 document_id 和 max(last_modified_time) 分組來做到這一點，并且在 (document_id) 上按 last_modified_time 排序（但它只給我每個 document_id 一條記錄 - 在我的情況下，我必須選擇 2 條具有相同 0ea8329ff9efe8a3052264fa494243b6323ae433 document_id 的記錄導致它有 last_modified_time 2018-12-13 09:12:24.17 )
uj5u.com熱心網友回復：
嘗試類似：
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
見dbfiddle
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/519438.html
標籤：PostgreSQL