Maybe this may pose as a simple question, but well, I'm not able to figure out a solution
I have this prospective table:
Assume this data and Columns:
PERSONA FECHA_PERF SECUENCIA2 NIVEL4_RIESGO
---------+---------+---------+---------+-------
24226517 12.06.2018 53. 00
24226517 12.06.2018 52. 04
24226517 12.06.2018 51. 04
24226517 12.06.2018 26. 04
24226517 12.06.2018 15. 04
24226517 12.06.2018 14. 04
24226517 12.06.2018 7. 04
24226517 08.05.2018 91. 04
24226517 08.05.2018 90. 04
24226517 08.05.2018 89. 03
24226517 08.05.2018 78. 04
24226517 08.05.2018 77. 04
24226517 08.05.2018 69. 04
24226517 08.05.2018 68. 04
24226517 08.05.2018 67. 04
24226517 08.05.2018 66. 04
24226517 08.05.2018 65. 01
24226517 08.05.2018 64. 04
24226517 08.05.2018 63. 04
24226517 08.05.2018 62. 04
24226517 08.05.2018 61. 04
24226517 08.05.2018 60. 04
24226517 08.05.2018 52. 04
24226517 08.05.2018 51. 04
FECHA_PERF is a date field, Secuencia2 is an integer field and Nivel4_riesgo is a Char(2) field and persona is a char (9) field
I need to obtain the latest NIVEL4_RIESGO value that has been inserted into the table for each PERSONA field, I need to take into account both FECHA_PERF and SECUENCIA2 fields
The table has many other fields, but for the sake of resutls, these are the fields needed
and the query's desired result should be:
--------------+---------
PERSONA NIVEL4_RIESGO
--------------+---------
24226517 00
Which corresponds to this record:
---------+---------+---------+---------+-------
PERSONA FECHA_PERF SECUENCIA2 NIVEL4_RIESGO
---------+---------+---------+---------+-------
24226517 12.06.2018 53. 00
Could you please provide a possible way oo implement this? I have, somehow, this sorted with subqueries, but I'm wondering if there is a better way to do it, specially when it comes to performance. I've considered using DB2 temporary tables for each subquery, but on this shop, well, they are a bit esoteric about it and are not that happy if I use them
SELECT A.PERSONA, A.NIVEL4_RIESGO
FROM table1 AS A
INNER JOIN
( SELECT PERSONA
,MAX(CHAR(VARCHAR_FORMAT(B.FECHA_PERF,'YYYY-MM-DD'),10) ||
TRIM(TO_CHAR(B.SECUENCIA2,'00'))) AS VALORX
,MAX(FECHA_PERF)
,MAX(SECUENCIA2)
FROM DIGEIN.GIPERF_PERS_GPP AS B
WHERE PERSONA = '24226517'
AND B.NIVEL4_RIESGO <> ''
GROUP BY PERSONA
) AS C
ON A.PERSONA = C.PERSONA
AND CHAR(VARCHAR_FORMAT(A.FECHA_PERF,'YYYY-MM-DD'),10) ||
TRIM(TO_CHAR(A.SECUENCIA2,'00')) =
C.VALORX
This query result is:
---------+---------+---
PERSONA NIVEL4_RIESGO
---------+---------+---
24226517 00
Which is what i need.
If I understand correctly, this would typically be done using row_number():
select p.*
from (select p.*,
row_number() over (partition by persona
order by fecha_perf desc, secuencia2 desc
) as seqnum
from DIGEIN.GIPERF_PERS_GPP p
) p
where seqnum = 1;
If you only want the result for a single person, you can use the simpler:
select p.*
from DIGEIN.GIPERF_PERS_GPP p
where persona = XXX
order by fecha_perf desc, secuencia2 desc
fetch first 1 row only;