Clash Royale CLAN TAG#URR8PPP

How get the biggest of a param when info values in rows are the same? oracle

I have a small problem since two days ago with PL SQL developer Oracle 11g. I want to filter a query where the repeated ones are filtered and show the one with the highest value of TIME

I have the follow result from a Query consult:

If see some rows has same values the difference is HORA column, i want filter those results and get the biggest HORA

I'm trying the following scripts:

SELECT HORA, TIEMPO_GESTION, DIRECCION, TIPO_GESTION , INI_GESTION, FIN_GESTION, CLIENTE
FROM (SELECT To_char(TRACK.fecha_hora, 'HH24:MI:SS') HORA,
'' DIRECCION,
CASE
WHEN INDET.tiempo_gestion IS NULL THEN '-'
ELSE INDET.tiempo_gestion
END TIEMPO_GESTION,
CASE
WHEN INDET.tipo_gestion IS NULL THEN '-'
ELSE INDET.tipo_gestion
END TIPO_GESTION,
CASE
WHEN INDET.ini_gestion IS NULL THEN '-'
ELSE INDET.ini_gestion
END INI_GESTION,
CASE
WHEN INDET.fin_gestion IS NULL THEN '-'
ELSE INDET.fin_gestion
END FIN_GESTION,
CASE
WHEN INDET.cliente IS NULL THEN '-'
ELSE INDET.cliente
END CLIENTE,
CASE
WHEN INDET.cliente IS NULL THEN 0
ELSE Row_number() OVER (PARTITION BY INDET.tiempo_gestion, INDET.ini_gestion, INDET.fin_gestion, INDET.tipo_gestion
ORDER BY INDET.tipo_gestion)
END rn2
FROM app_tracking_edn TRACK
LEFT JOIN (SELECT D.fecha_hora_inicio INI_GESTION,
D.fecha_fin_inicio FIN_GESTION,
D.fec_hoja_ruta FECHA,
Substr(( To_timestamp(D.fecha_fin_inicio, 'DD/MM/YYYY HH24:MI:SS') - To_timestamp(D.fecha_hora_inicio, 'DD/MM/YYYY HH24:MI:SS') ), 12 , 8) TIEMPO_GESTION,
D.nom_cli CLIENTE,
U.cod_usuario USUARIO,
Z.des_parametro TIPO_GESTION
FROM cre_hoja_ruta_det D
INNER JOIN cre_ejecutivo_neg E
ON D.cod_ejecutivo = E.cod_ejecutivo AND
E.ind_baja = 'N'
INNER JOIN mae_usuario U
ON E.cod_personal = U.cod_personal
INNER JOIN mae_par_hoj_ruta Z
ON Z.cod_parametro = D.cod_tip_act AND
Z.nom_parametro = 'TIP_ACTIVIDAD') INDET
ON TRACK.usuario_edn = INDET.usuario AND
To_char(TRACK.fecha_hora, 'DD/MM/YYYY') = To_char(INDET.fecha, 'DD/MM/YYYY') AND
To_char(TRACK.fecha_hora, 'HH24:MI:SS') BETWEEN Substr(INDET.ini_gestion, 12, 8)
AND Substr(INDET.fin_gestion, 12, 8)
WHERE To_char(TRACK.fecha_hora, 'DD/MM/YYYY') = '01/05/2018' AND
TRACK.usuario_edn = Upper('AJIMENEZ')
ORDER BY TRACK.fecha_hora ASC) A
WHERE RN2 BETWEEN -1 AND 1
GROUP BY HORA, TIEMPO_GESTION, DIRECCION, TIPO_GESTION , INI_GESTION, FIN_GESTION, CLIENTE


And i'm getting:

I get the smallest TIME, but I'm looking for the longest, anyone can help me please?

please simplify your question. hard to read such a long one.
– Vamsi Prabhala
9 hours ago

Ok @VamsiPrabhala I already simplified it, as much as I could
– Carlos Reyes
9 hours ago

At a quick glance. Seems to me that you just need to change the order in that row_number for RN2 : ORDER BY TRACK.FECHA_HORA DESC
– LukStorms
9 hours ago

ORDER BY TRACK.FECHA_HORA DESC

Don't post code or data as images - instead, post them as formatted text. Read this page for help on how to format text - generally, I like to see code and data formatted as code. Thanks.
– Bob Jarvis
6 hours ago

Suggestion: instead of converting dates to character strings using format DD/MM/YYYY in order to compare them, just TRUNC the dates to eliminate the time portion and compare them as dates, e.g. instead of To_char(TRACK.fecha_hora, 'DD/MM/YYYY') = To_char(INDET.fecha, 'DD/MM/YYYY'), use TRUNC(TRACK.FECHA_HORA) = TRUNC(INDET.FECHA). Does the same thing, but should be faster.
– Bob Jarvis
6 hours ago

DD/MM/YYYY

TRUNC

To_char(TRACK.fecha_hora, 'DD/MM/YYYY') = To_char(INDET.fecha, 'DD/MM/YYYY')

TRUNC(TRACK.FECHA_HORA) = TRUNC(INDET.FECHA)

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.