Select only MAX date record

Question

I have this huge (not so huge) SQL query:

SELECT DISTINCT TABVEIC.NU_STATUS,
                S.CD_STATUS_SMP,
                S.CD_SMP,
                S.NU_SMP,
                S.NU_ANO_SMP,
                CASE
                    WHEN TABVEIC.CD_SMP IS NOT NULL THEN
                     SPLIT(FNC_TMA_SINALEIRA(TABVEIC.CD_SMP), 3, ';')
                    ELSE
                     '00:00'
                END AS TMA_ONLINE,
                CASE
                    WHEN (SPOA.DT_SAIDA_PONTO IS NULL OR
                         SPEA.DT_SAIDA_PONTO IS NULL) THEN
                     (CASE
                         WHEN EXISTS (SELECT 1
                                 FROM TB_SMP
                                WHERE CD_SMP = TABVEIC.CD_SMP
                                  AND CD_STATUS_SMP IN (2, 9, 11)) THEN
                          FNC_RETORNA_SINALEIRA_SMP(TABVEIC.CD_SMP)
                         ELSE
                          -1
                     END)
                    ELSE
                     6
                END SINALEIRA,
                CASE
                    WHEN EXISTS (SELECT 1
                            FROM TB_ENTIDADE_RECURSO_ENTIDADE EE
                           WHERE EE.CD_ENTIDADE =
                                 (SELECT CD_ENTIDADE_CLIENTE
                                    FROM TB_SMP
                                   WHERE CD_SMP = TABVEIC.CD_SMP)
                             AND EE.CD_ENTIDADE_RECURSO = 81) THEN
                     'SINALEIRA'
                END STATUS_ATUAL,
                TABVEIC.NU_LATITUDE,
                TABVEIC.NU_LONGITUDE,
                TABVEIC.DS_PLACA_VEICULO,
                CASE
                    WHEN S.CD_STATUS_SMP IN (2, 9, 11) THEN
                     S.DS_DOCUMENTO_NF
                    ELSE
                     NULL
                END DS_DOCUMENTO_NF,
                CASE
                    WHEN S.CD_STATUS_SMP IN (2, 9, 11) THEN
                     PGORIGEM.DS_APELIDO
                    ELSE
                     NULL
                END DS_ORIGEM_APELIDO,
                CASE
                    WHEN S.CD_STATUS_SMP IN (2, 9, 11) THEN
                     (CASE
                         WHEN S.DT_SAIDA_PONTO_ORIGEM IS NULL THEN
                          S.DT_ENTRADA_PONTO_ORIGEM
                         ELSE
                          SPE.DT_ENTRADA_PONTO
                     END)
                    ELSE
                     TABVEIC.DT_TIME_POSITION
                END AS DT_ENTRADA_ALVO,
                CASE
                    WHEN S.DT_SAIDA_PONTO_ORIGEM IS NULL THEN
                     SPOA.DT_ENTRADA_PONTO
                    ELSE
                     SPEA.DT_ENTRADA_PONTO
                END AS DT_ENTRADA_SUB_ALVO,
                CASE
                    WHEN S.DT_SAIDA_PONTO_ORIGEM IS NULL THEN
                     SPOA.DT_SAIDA_PONTO
                    ELSE
                     SPEA.DT_SAIDA_PONTO
                END AS DT_SAIDA_SUB_ALVO,
                CASE
                    WHEN S.DT_SAIDA_PONTO_ORIGEM IS NULL THEN
                     'PONTO DE ORIGEM IS NULL'
                    ELSE
                     'PONTO DE ORIGEM IS NOT NULL'
                END AS DT_SAIDA_SUB_ALVO,
                ATUALIZADO
  FROM
  (SELECT
                       (SELECT MAX(SP.CD_SMP)
                       FROM TB_SMP_VEICULO SP
                       WHERE SP.CD_VEICULO = VC.CD_VEICULO) CD_SMP,
                       VC.NU_STATUS,
                       VC.DS_PLACA_VEICULO,
                       RAS.NU_LATITUDE,
                       RAS.NU_LONGITUDE,
                       RAS.DT_TIME_POSITION,
                       SYSDATE,
                       -- COUNT(1) AS REGISTROS,   -- CD_PONTO_GEO = 3004949
                       CASE
                           WHEN TRUNC((SYSDATE - RAS.DT_TIME_POSITION) * 1440, 2) > 25 THEN
                            'N'
                           ELSE
                            'S'
                       END AS ATUALIZADO
                  FROM TB_VEICULO VC
                 INNER JOIN TB_RASTREADOR RAS
                    ON RAS.CD_RASTREADOR = VC.CD_RASTREADOR
                 WHERE VC.CD_ENTIDADE IN (178, 204, 821, 896, 999, 1000, 1006, 1025, 1031, 1058, 1079, 1094, 1095, 1103, 1318, 1539, 1630, 1644, 1650, 1653, 1679, 1684, 1751, 1755, 1787, 1794, 1795, 1808, 1810, 1835, 1949, 1988, 2015, 2030, 2035, 2069, 2072, 2115, 2127, 2138, 2144, 2223, 7377, 7462, 7790, 7925, 7930, 8161, 8186, 8199, 8256, 8257, 8258, 8511, 8552, 8769, 8829, 8946, 8963, 9440, 9441, 9520, 9535, 9536, 9543, 9700, 9838, 9887, 9888, 9889, 9890, 9933, 9934, 9935, 9936, 9937, 9938, 9939, 9943, 9944, 9959, 10084, 10353, 10373, 10397, 10663, 10681, 10803, 10804, 10805, 10807, 10808, 10854, 10922, 10926, 10952, 10962, 10963, 11000, 11204, 11328, 11329, 11330, 11400, 11453, 11480, 11484, 11485, 11493, 11521, 11523, 11540, 11559, 11610, 11628, 12007, 12099, 12171, 12182, 12188, 12189, 12191, 12192, 12238, 12247, 12248, 12249, 12264, 12269, 12277, 12278, 12288, 12320, 12325, 12329, 12330, 12335, 12380, 12385, 12386, 12387, 12419, 12423, 12438, 12446, 12449, 12492, 12493, 12494, 12504, 12532, 12541, 12542, 12558, 12574, 12632, 12653, 12660, 12673, 12682, 12711, 12725, 12727, 12732, 12743, 12750, 12767, 12774, 13393, 22858, 23176, 23254, 23522, 23523, 23524, 23532, 23576, 23577, 23581, 23587, 23602, 23607, 23637, 23638, 23639, 23661, 23668, 23669, 23678, 23680, 23686, 23696, 23703, 23715, 23733, 23748, 23761, 23802, 23808, 23815, 23827, 23828, 23874, 23936, 24074, 24075, 24091, 24118, 24154, 24162, 24204, 24219, 24232, 24254, 24255, 24261, 24271, 24290, 24301, 24309, 24370, 24382, 24385, 24394, 24395, 24396, 24455, 24530, 24541, 24673, 24674, 24675, 24739, 24765, 24805, 24813, 24897, 24921, 24936, 24989, 24993, 24994, 24995, 25084, 25153, 25193, 25247, 25294, 25306, 25313, 25314, 25355, 25362, 25436, 25764, 25854, 25894, 25895, 26075, 26082, 0)
                   AND (RAS.NU_LATITUDE IS NOT NULL AND RAS.NU_LONGITUDE IS NOT NULL)
                   AND VC.NU_STATUS = 1
                   AND RAS.FL_LOCALIZADOR = 'N'
                   AND RAS.NU_STATUS = 1
                   AND FNC_VALIDA_DENTRO_PONTO(&CD_PONTO_GEO,
                                               NU_LATITUDE,
                                               NU_LONGITUDE) = 1) TABVEIC
      LEFT JOIN TB_SMP S
        ON S.CD_SMP = TABVEIC.CD_SMP
      LEFT JOIN TB_SMP_PONTO_ORIGEM_AREA SPOA
        ON SPOA.CD_SMP = TABVEIC.CD_SMP
      LEFT JOIN TB_SMP_PONTO_ENTREGA SPE
        ON SPE.CD_SMP = S.CD_SMP
      LEFT JOIN TB_SMP_PONTO_ENTREGA_AREA SPEA
        ON SPEA.CD_SMP_PONTO_ENTREGA = SPE.CD_SMP_PONTO_ENTREGA     
      LEFT JOIN TB_PONTO_GEOREFERENCIADO PGORIGEM
        ON PGORIGEM.CD_PONTO_GEOREFERENCIADO = S.CD_PONTO_GEO_ORIGEM AND S.CD_STATUS_SMP IN (2, 9)

My problem is I must to get only the last date from this LEFT JOIN TB_SMP_PONTO_ORIGEM_AREA SPOA or this LEFT JOIN TB_SMP_PONTO_ENTREGA_AREA joins, its according to this condition (that is at body of SQL):

CASE WHEN S.DT_SAIDA_PONTO_ORIGEM IS NULL THEN SPOA.DT_ENTRADA_PONTO ELSE SPEA.DT_ENTRADA_PONTO END AS DT_ENTRADA_SUB_ALVO

I've tried put this where statement at the end of select:

WHERE SPEA.DT_ENTRADA_PONTO IN (select MAX(SPEnt.DT_ENTRADA_PONTO) FROM TB_SMP_PONTO_ENTREGA_AREA SPEnt where SPEnt.CD_SMP_PONTO_ENTREGA = SPE.CD_SMP_PONTO_ENTREGA)

and it stops showing a bunch of times the same record that has different dates, but doesn't bring the other results.

My result using where:

My result without where:

In the second image we can se a bunch of record from id: 1254190

So my question is how can I retrieve non duplicated records ignoring the date "DT_ENTRADA_PONTO", or getting its MAX value?

Thank you guys!

EDIT

Solution:

I've changed only these JOIN's:

    LEFT JOIN TB_SMP S
        ON S.CD_SMP = TABVEIC.CD_SMP
    LEFT JOIN (SELECT SPOADESC.CD_SMP, SPOADESC.DT_SAIDA_PONTO, SPOADESC.DT_ENTRADA_PONTO
                        FROM (SELECT * FROM TB_SMP_PONTO_ORIGEM_AREA SPOA 
                        WHERE SPOA.DT_ENTRADA_PONTO IN (SELECT MAX(DT_Entrada_ponto) from TB_SMP_PONTO_ENTREGA)
                        ) SPOADESC ) SPOADESC2
        ON SPOADESC2.CD_SMP = TABVEIC.CD_SMP

    LEFT JOIN TB_SMP_PONTO_ENTREGA SPE
        ON SPE.CD_SMP = S.CD_SMP
    LEFT JOIN (SELECT SPEADESC.CD_SMP_PONTO_ENTREGA, SPEADESC.DT_SAIDA_PONTO, SPEADESC.DT_ENTRADA_PONTO
                        FROM (SELECT * FROM TB_SMP_PONTO_ENTREGA_AREA SPEA 
                             WHERE SPEA.DT_ENTRADA_PONTO IN (SELECT MAX(DT_Entrada_ponto) from TB_SMP_PONTO_ENTREGA)
                             ) SPEADESC ) SPEADESC2
        ON SPE.CD_SMP_PONTO_ENTREGA = SPEADESC2.CD_SMP_PONTO_ENTREGA

And now it works!

Answer 1

Not sure of the Oracle syntax, but when joining to the table, join on the PK = the PK of a TOP 1 subquery of the table, order by the date column descending.

Here's how I would do it in SQL Server:

LEFT JOIN MyTable t1 ON t1.PK=(
  SELECT TOP 1 PK
  FROM MyTable t2
  WHERE SomeCondition=true
  ORDER BY DateColumn DESC
)