Oracle 以某字段分组，以某字段排序，取第一条,加分页

select RH_ID,
       RH_FID,
       RH_EM_FID,
       RSM_FIRST_VALUE,
       RSM_PRESENT,
       RSM_COLLECTION_TIME,
       RSM_RELATIVE,
       RSM_X,
       RSM_Y,
       RSM_H,
       RSM_INITIAL_VALUE,
       RSM_CURRENT_VALUE,
       RSM_CURRENT_SOILRESSURE,
       RSM_CUMULATIVE_RAINFALL,
       RSM_TEMPERATURE,
       RSM_HUMIDITY,
       RSM_RAINFALL,
       RSM_TYPE,
       RSM_ESTABLISH_PEOPLE,
       RSM_ESTABLISH_TIME,
       RSM_DELFLAG,
       MI_FID,
       RH_EI_MODEL,
       RSM_FIRST_TYPE,
       rownum_
  from (select RSM_ID AS RH_ID,
               RSM_FID AS RH_FID,
               (select EMI_NUMBER
                  from EQUIPMENT_INFO
                 where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID) AS RH_EM_FID,
               RSM_FIRST_VALUE,
               RSM_PRESENT,
               to_char(RSM_COLLECTION_TIME, 'yyyy-MM-dd') RSM_COLLECTION_TIME,
               RSM_RELATIVE,
               RSM_X,
               RSM_Y,
               RSM_H,
               RSM_INITIAL_VALUE,
               RSM_CURRENT_VALUE,
               RSM_CURRENT_SOILRESSURE,
               RSM_CUMULATIVE_RAINFALL,
               RSM_TEMPERATURE,
               RSM_HUMIDITY,
               RSM_RAINFALL,
               RSM_TYPE,
               RSM_ESTABLISH_PEOPLE,
               to_char(RSM_ESTABLISH_TIME, 'yyyy-MM-dd') RSM_ESTABLISH_TIME,
               RSM_DELFLAG,
               (select TMP_NAME
                  from TERM_MEASUREPOINKS
                 where TERM_MEASUREPOINKS.TMP_FID =
                       (select EMI_TMP_FID
                          from EQUIPMENT_INFO
                         where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID)) AS MI_FID,
               (select EMI_MODEL
                  from EQUIPMENT_INFO
                 where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID) AS RH_EI_MODEL,
               (select EMI_NAME
                  from EQUIPMENT_INFO
                 where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID) AS RSM_FIRST_TYPE,
               row_number() over(partition by RSM_EM_FID order by RSM_COLLECTION_TIME desc) as rownum_ 
          from REAL_TIME_MONITOR
         where 1 = 1
           and RSM_DELFLAG = '0'
           and (RSM_EM_FID in ('EMI201911041942158697F7A5',
                               'EMI2019110609422644174A83',
                               'EMI2019110615565882345D58',
                               'EMI2019110409113474CEC67B'))) row_
 WHERE row_.rownum_ between 0 and 200 and rownum_=1

select count(1)
  from (select RSM_ID,
               RSM_FID,
               (select EMI_NUMBER
                  from EQUIPMENT_INFO
                 where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID) AS RSM_EM_FID,
               RSM_FIRST_VALUE,
               RSM_PRESENT,
               to_char(RSM_COLLECTION_TIME, 'yyyy-MM-dd') RSM_COLLECTION_TIME,
               RSM_RELATIVE,
               RSM_X,
               RSM_Y,
               RSM_H,
               RSM_INITIAL_VALUE,
               RSM_CURRENT_VALUE,
               RSM_CURRENT_SOILRESSURE,
               RSM_CUMULATIVE_RAINFALL,
               RSM_TEMPERATURE,
               RSM_HUMIDITY,
               RSM_RAINFALL,
               RSM_TYPE,
               RSM_INITIAL_WATER,
               RSM_CURRENT_WATER,
               RSM_DEPTH,
               RSM_ESTABLISH_PEOPLE,
               to_char(RSM_ESTABLISH_TIME, 'yyyy-MM-dd') RSM_ESTABLISH_TIME,
               RSM_DELFLAG,
               (select TMP_NAME
                  from TERM_MEASUREPOINKS
                 where TERM_MEASUREPOINKS.TMP_FID =
                       (select EMI_TMP_FID
                          from EQUIPMENT_INFO
                         where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID)) AS point,
               (select EMI_MODEL
                  from EQUIPMENT_INFO
                 where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID
                   and EMI_DELFLAG = '0') AS model,
               (select EMI_NAME
                  from EQUIPMENT_INFO
                 where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID
                   and EMI_DELFLAG = '0') AS type,
               row_number() over(partition by RSM_EM_FID order by RSM_COLLECTION_TIME desc) as rownum_
          from REAL_TIME_MONITOR
         where 1 = 1
           and RSM_DELFLAG = '0'
           and (RSM_EM_FID in ('EMI201911041942158697F7A5',
                               'EMI2019110609422644174A83',
                               'EMI2019110615565882345D58',
                               'EMI2019110409113474CEC67B')))
 where rownum_ = 1

参考sql

select * from (select 表.*, row_number() over(partition by 以此字段为分组  order by 以此字段排序 desc) rn

from 表) where rn=1   --表示取第一个