title: Convert keep dense_rank from Oracle query into postgres
body: <p>I'm trying to convert the following Oracle query into Postgres</p>
  
  <pre><code>select
          this_.GLOBAL_TRANSACTION_ID as y0_,
          this_.BUSINESS_IDENTIFIER as y1_,
          this_.ENVIRONMENT as y2_,
          count(*) as y3_,
          this_.HOST_NAME as y4_,
          listagg(process,
          ', ') within
      group (order by
          date_time) as process,
          min(this_.DATE_TIME) as y6_,
          max(this_.DATE_TIME) as y7_,
          max(status)keep(dense_rank last
      order by
          date_time,
          decode(status,
          'COMPLETED',
          'd',
          'FAILED',
          'c',
          'TERMINATED',
          'b',
          'STARTED',
          'a',
          'z')) as status
      from
          ACTIVITY_MONITOR_TRANSACTION this_
      where
          this_.DATE_TIME between ? and ?
          and 1=1
      group by
          this_.GLOBAL_TRANSACTION_ID,
          this_.BUSINESS_IDENTIFIER,
          this_.ENVIRONMENT,
          this_.HOST_NAME,
          global_transaction_id,
          business_identifier,
          global_transaction_id,
          business_identifier
      order by
          y7_ asc
  </code></pre>
  
  <p>the problem is I don't know how to convert this block:</p>
  
  <pre><code>max(status)keep(dense_rank last
      order by
          date_time,
          decode(status,
          'COMPLETED',
          'd',
          'FAILED',
          'c',
          'TERMINATED',
          'b',
          'STARTED',
          'a',
          'z')) as status
  </code></pre>
  
  <p>The aim of this block is to get the <strong>latest</strong> status, and in case of exact same time (it is possible!) assign the status following the order above.</p>
  
  <pre><code>This is an example of data:
        ID        DATA_TIME          GLOBAL_TRANSACTION_ID   STATUS
  ===================================================================
   54938456;"2015-04-20 09:39:27";"8d276718-eca7-4fd0-a266 ;"STARTED"
   54938505;"2015-04-20 09:39:27";"8d276718-eca7-4fd0-a266 ;"COMPLETED"
   54938507;"2015-04-20 09:39:27";"8d276718-eca7-4fd0-a266 ;"FAILED"
   54938507;"2015-04-20 09:38:25";"8d276718-eca7-4fd0-a266 ;"FAILED"
  </code></pre>
  
  <p>The status should be "COMPLETED" so my query should return, among other rows, the following:</p>
  
  <pre><code> GLOBAL_TRANSACTION_ID    COUNT    (...)       STATUS
  =====================================================
   8d276718-eca7-4fd0-a266    4      (...)     COMPLETED
  </code></pre>
  
  <p>I have tried splitting the query into 2:</p>
  
  <pre><code>select
      this_.GLOBAL_TRANSACTION_ID as y0_,
      this_.BUSINESS_IDENTIFIER as y1_,
      this_.ENVIRONMENT as y2_,
      count(*) as y3_,
      this_.HOST_NAME as y4_,
      array_to_string(array_agg(distinct process),
      ',') as process,
      min(this_.DATE_TIME) as y6_,
      max(this_.DATE_TIME) as y7_,
      max(this_.STATUS) as y8_
  from
      ACTIVITY_MONITOR_TRANSACTION this_
  where
      this_.DATE_TIME between ? and ?
  group by
      this_.GLOBAL_TRANSACTION_ID,
      this_.BUSINESS_IDENTIFIER,
      this_.ENVIRONMENT,
      this_.HOST_NAME,
      global_transaction_id,
      business_identifier
  order by
      y7_ desc limit ?
  </code></pre>
  
  <p>and then</p>
  
  <pre><code>select
      status
  from
      activity_monitor_transaction
  where
      GLOBAL_TRANSACTION_ID=?
  order by
      date_time DESC,
      CASE status
          WHEN 'COMPLETED'THEN 'd'
          WHEN 'FAILED' THEN 'c'
          WHEN 'TERMINATED' THEN 'b'
          WHEN 'STARTED' THEN 'a'
          ELSE 'z'
      END DESC LIMIT 1
  </code></pre>
  
  <p>But this is causing me performance issues as I have to execute the second query once per row.</p>
  
  <p>This is the table script for postgres:</p>
  
  <pre><code>CREATE TABLE activity_monitor_transaction
  (
    id numeric(11,0) NOT NULL,
    date_time timestamp(6) without time zone NOT NULL,
    global_transaction_id character varying(40) NOT NULL,
    repost_flag character(1) NOT NULL DEFAULT 'N'::bpchar,
    environment character varying(20),
    transaction_mode character varying(20),
    status character varying(20),
    step character varying(80),
    event character varying(20),
    event_code character varying(20),
    event_subcode character varying(20),
    summary character varying(200),
    business_identifier character varying(80),
    alternate_business_identifier character varying(80),
    domain character varying(20),
    process character varying(80),
    service_name character varying(80),
    service_version character varying(20),
    detail text,
    app_name character varying(80),
    app_user character varying(20),
    host_name character varying(80),
    thread_name character varying(200),
    CONSTRAINT activity_monitor_transact_pk PRIMARY KEY (id)
    USING INDEX TABLESPACE actmon_data
  )
  </code></pre>
  
  <p>and some data:</p>
  
  <pre><code>insert into ACTIVITY_MONITOR_TRANSACTION values 
  (54938456,'2015-04-20 09:39:27','8d276718-eca7-4fd0-a266-d465181f911a','N','Perf','','STARTED','servicereq.p2p.rso.blaze.dedup.in.channel','PROCESS','','','','3100729','51174628','ERP','servicereq-p2p-rso-blaze','servicereq-p2p-rso-blaze','1.0.0-SNAPSHOT','','servicereq-p2p-rso-blaze','CIC','intintprf20','SimpleAsyncTaskExecutor-88177');
  
  insert into ACTIVITY_MONITOR_TRANSACTION values 
  (54938505,'2015-04-20 09:45:27','8d276718-eca7-4fd0-a266-d465181f911a','N','Perf','','COMPLETED','servicereq.p2p.rso.blaze.service.out.channel','PROCESS','','','','3100729','51174628','ERP','servicereq-p2p-rso-blaze','servicereq-p2p-rso-blaze','1.0.0-SNAPSHOT','','servicereq-p2p-rso-blaze','CIC','intintprf20','SimpleAsyncTaskExecutor-88177');
  
  insert into ACTIVITY_MONITOR_TRANSACTION values 
  (54938507,'2015-04-20 09:45:27','8d276718-eca7-4fd0-a266-d465181f911a','N','Perf','','FAILED','inputChannel','PROCESS','','','','3100729','','ERP','servicereq-p2p-rso-blaze','servicereq-p2p-rso-blaze','1.0.0-SNAPSHOT','','servicereq-p2p-rso-blaze','CIC','intintprf20','SimpleAsyncTaskExecutor-88177');
  </code></pre>
  
  <p>Is there any way to mimic the <strong>keep dense_rank</strong> block into postgres in order to have just one query?</p>