SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

MAX(ROW_NUMBER() over..) how?

RengudiJun 23 2023

Hi Gurus,

Greetings.

sample input from a result of this query

SELECT src.name,
SUBSTR (src.name, INSTR (src.name, '/') + 1) net_prefix_length,
src.U_START_IP_INT,
src.U_END_IP_INT,
sp_data.client_ip ip_from_splunk_report,
am_utility.quad2int (sp_data.client_ip) client_ip_int,
ROW_NUMBER ()
OVER (PARTITION BY sp_data.client_ip
ORDER BY src.U_END_IP_INT DESC, src.U_START_IP_INT) AS RANKING
FROM (SELECT *
FROM DMZ_INTERNAL_VIEW_TOP_TALKERS
WHERE client_ip IN ('164.101.214.200', '164.102.187.117')) sp_data
LEFT JOIN IP_NETWORK@"db_link" src
ON src.U_START_IP_INT <=
am_utility.quad2int (sp_data.client_ip)
AND src.U_END_IP_INT >= am_utility.quad2int (sp_data.client_ip)

(NAME,NET_PREFIX_LENGTH,U_START_IP_INT,U_END_IP_INT,IP_FROM_SPLUNK_REPORT,CLIENT_IP_INT,RANKING) AS
SELECT '164.100.0.0/14' AS NAME,14 AS NET_PREFIX_LENGTH,1080295424 AS U_START_IP_INT,1080557567 AS U_END_IP_INT,'164.101.214.200' AS IP_FROM_SPLUNK_REPORT,1080415944 AS CLIENT_IP_INT,1 AS RANKING FROM dual
UNION ALL
SELECT '164.101.192.0/19',19,1080410112,1080418303,'164.101.214.200',1080415944,2 FROM dual
UNION ALL
SELECT '164.101.208.0/21',21,1080414208,1080416255,'164.101.214.200',1080415944,3 FROM dual
UNION ALL
SELECT '164.101.214.192/28',28,1080415936,1080415951,'164.101.214.200',1080415944,4 FROM dual
UNION ALL
SELECT '164.100.0.0/14',14,1080295424,1080557567,'164.102.187.117',1080474485,1 FROM dual
UNION ALL
SELECT '164.102.0.0/16',16,1080426496,1080492031,'164.102.187.117',1080474485,2 FROM dual
UNION ALL
SELECT '164.102.128.0/18',18,1080459264,1080475647,'164.102.187.117',1080474485,3 FROM dual
UNION ALL
SELECT '164.102.160.0/19',19,1080467456,1080475647,'164.102.187.117',1080474485,4 FROM dual
UNION ALL
SELECT '164.102.184.0/22',22,1080473600,1080474623,'164.102.187.117',1080474485,5 FROM dual
UNION ALL
SELECT '164.102.186.0/23',23,1080474112,1080474623,'164.102.187.117',1080474485,6 FROM dual;

expected output


+--------------------+-------------------+----------------+--------------+-----------------------+---------------+---------+
| NAME               | NET_PREFIX_LENGTH | U_START_IP_INT | U_END_IP_INT | IP_FROM_SPLUNK_REPORT | CLIENT_IP_INT | RANKING |
+====================+===================+================+==============+=======================+===============+=========+
| 164.101.214.192/28 | 28                | 1080415936     | 1080415951   | 164.101.214.200       | 1080415944    | 4       |
+--------------------+-------------------+----------------+--------------+-----------------------+---------------+---------+
| 164.102.186.0/23   | 23                | 1080474112     | 1080474623   | 164.102.187.117       | 1080474485    | 6       |
+--------------------+-------------------+----------------+--------------+-----------------------+---------------+---------+


how to change sample input select query to achieve  this?

Thanks

This post has been answered by L. Fernigrini on Jun 23 2023

Jump to Answer

Added on Jun 23 2023

#plsql, #sql

15 comments

3,231 views