Find out unique top record based on two columns duplicate value

Question

Below is some data

Now I want to retrieve only those records who has unique current_position_latitude and current_position_longitude and top record by using order by for sys_movement_evt_id. That is below output I required.

My current query is

select
        CURRENT_POSITION_LATITUDE,
        CURRENT_POSITION_LONGITUDE,
        SYS_EVT_TARGET_ID,
        SYS_MOVEMENT_EVT_ID
from TMS_MOVEMENT_EVT
where SYS_EVT_TARGET_ID = 10245 and
      CURRENT_POSITION_LATITUDE is not null and CURRENT_POSITION_LATITUDE != 0 and 
      CURRENT_POSITION_LONGITUDE is not null and CURRENT_POSITION_LONGITUDE != 0 

Please let me know how can I achieve required output.

Answer 1

You can achieved this using RANK() function

with CTE_DATA AS (
                  SELECT
                         CURRENT_POSITION_LATITUDE,
                         CURRENT_POSITION_LONGITUDE,
                         SYS_EVT_TARGET_ID,
                         SYS_MOVEMENT_EVT_ID
                         RANK() OVER(PARTION BY CURRENT_POSITION_LATITUDE, 
                                                 CURRENT_POSITION_LONGITUDE
                                      ORDER BY SYS_MOVEMENT_EVT_ID DESC) LAT_RANK
                  FROM TMS_MOVEMENT_EVT
                 )
SELECT
      CURRENT_POSITION_LATITUDE,
      CURRENT_POSITION_LONGITUDE,
      SYS_EVT_TARGET_ID,
      SYS_MOVEMENT_EVT_ID
FROM CTE_DATA
WHERE LAT_RANK = 1

Some info on using RANK()