Hi Gurus,
My input:-
SQL> set lines 20000
SQL> select host_name,listagg(ip_address,',') within group (order by ip_address) as ip_address from (with t(host_name,IP) AS(
SELECT '_usdcs.vuba.com.' AS host_name,'53.38.200.154' AS IP from dual
UNION ALL
SELECT '_usdcs.vuba.com.','64.100.37.86' from dual
UNION ALL
SELECT '_usdcs.vuba.com.','23.38.200.157' from dual
UNION ALL
SELECT '_usdcs.vuba.com.','93.37.248.74'from dual
UNION ALL
SELECT '_usdcs.vuba.com.','52.36.56.70' from dual
UNION ALL
SELECT '_usdcs.vuba.com.','53.36.131.78' from dual
UNION ALL
SELECT '_usdcs.vuba.com.','53.36.131.79' from dual
)
select host_name, 'REDIRECT' || ' ' ||round(dbms_random.value()*100)* ROW_NUMBER () OVER (PARTITION BY host_name ORDER BY host_name) || '=>' || IP AS IP_ADDRESS from t)
group by host_name
Result set:-
HOST_NAME IP_ADDRESS
_usdcs.vuba.com. REDIRECT 14=>53.38.200.154,REDIRECT 216=>23.38.200.157,REDIRECT 224=>53.36.131.79,REDIRECT 434=>93.37.248.74,REDIRECT 445=>52.36.56.70,REDIRECT 64=>64.100.37.86,REDIRECT 66=>53.36.131.78
Requirement:
I am looking for the result set that should be sorted according to the product of
round(dbms_random.value()*100)* ROW_NUMBER() for IP_ADDRESS column as
REDIRECT 14=>53.38.200.154,REDIRECT 64=>64.100.37.86,REDIRECT 66=>53.36.131.78,REDIRECT 216=>23.38.200.157,REDIRECT 224=>53.36.131.79,REDIRECT 434=>93.37.248.74,REDIRECT 445=>52.36.56.70;
Thanks!