Forum Stats

  • 3,734,039 Users
  • 2,246,863 Discussions
  • 7,857,010 Comments

Discussions

Query: Order by IP

573579
573579 Member Posts: 2
edited November 2010 in SQL & PL/SQL
Sorry for my bad english at first ;)

I have the followring Query:

SELECT * FROM GN_SERVER ORDER BY HAUPT_IP

and i need to sort this by the IP Adress, that the result show like this:

83.133.96.2
83.133.96.3
83.133.96.10
83.133.96.20
83.133.97.2
83.133.97.3
83.133.97.10
83.133.98.2
83.133.98.3
83.133.98.10
and so on

but i cant make ORDER BY HAUPT_IP this will return a false result.

How can i make the correct query?

Thanks for help.

Comments

  • Avinash Tripathi
    Avinash Tripathi Member Posts: 1,614 Bronze Badge
    Hi,
    Try this:

    WITH T AS (
    SELECT '83.133.96.2' IP FROM DUAL UNION ALL
    SELECT '83.133.96.3' FROM DUAL UNION ALL
    SELECT '83.133.96.10' FROM DUAL UNION ALL
    SELECT '83.133.98.10' FROM DUAL UNION ALL
    SELECT '83.133.97.2' FROM DUAL UNION ALL
    SELECT '83.133.97.3' FROM DUAL UNION ALL
    SELECT '83.133.97.10' FROM DUAL UNION ALL
    SELECT '83.133.98.2' FROM DUAL UNION ALL
    SELECT '83.133.98.3' FROM DUAL UNION ALL
    SELECT '83.133.96.20' FROM DUAL )


    SELECT IP FROM t ORDER BY TO_NUMBER(REPLACE(IP,'.'))


    Regards
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    SQL> create table gn_server
    2 as
    3 select '1.1.1.1' haupt_ip from dual union all
    4 select '10.10.10.10' from dual union all
    5 select '2.2.2.2' from dual union all
    6 select '20.20.20.100' from dual union all
    7 select '20.20.20.1' from dual union all
    8 select '100.100.100.100' from dual
    9 /

    Tabel is aangemaakt.

    SQL> select haupt_ip
    2 from gn_server
    3 order by haupt_ip
    4 /

    HAUPT_IP
    ---------------
    100.100.100.100
    10.10.10.10
    1.1.1.1
    20.20.20.1
    20.20.20.100
    2.2.2.2

    6 rijen zijn geselecteerd.

    SQL> SELECT haupt_IP FROM gn_server ORDER BY TO_NUMBER(REPLACE(haupt_IP,'.'))
    2 /

    HAUPT_IP
    ---------------
    1.1.1.1
    2.2.2.2
    20.20.20.1
    10.10.10.10
    20.20.20.100
    100.100.100.100

    6 rijen zijn geselecteerd.

    SQL> select haupt_ip
    2 from gn_server
    3 order by to_number(substr(haupt_ip,1,instr(haupt_ip,'.',1,1)-1))
    4 , to_number(substr(haupt_ip,instr(haupt_ip,'.',1,1)+1,instr(haupt_ip,'.',1,2)-instr(haupt_ip,'.',1,1)-1))
    5 , to_number(substr(haupt_ip,instr(haupt_ip,'.',1,2)+1,instr(haupt_ip,'.',1,3)-instr(haupt_ip,'.',1,2)-1))
    6 , to_number(substr(haupt_ip,instr(haupt_ip,'.',1,3)+1))
    7 /

    HAUPT_IP
    ---------------
    1.1.1.1
    2.2.2.2
    10.10.10.10
    20.20.20.1
    20.20.20.100
    100.100.100.100

    6 rijen zijn geselecteerd.
    Regards,
    Rob.
  • Laurent Schneider
    Laurent Schneider Member Posts: 5,219
    SELECT IP,
      lpad(substr(ip,1,instr(ip,'.')-1),3,'0')||'.'||
      lpad(substr(ip,instr(ip,'.')+1,instr(ip,'.',1,2)-instr(ip,'.')-1),3,'0')||'.'||
      lpad(substr(ip,instr(ip,'.',1,2)+1,instr(ip,'.',1,3)-instr(ip,'.',1,2)-1),3,'0')||'.'||
      lpad(substr(ip,instr(ip,'.',1,3)+1),3,'0') FORMATTED_IP
     from t
    order by
      lpad(substr(ip,1,instr(ip,'.')-1),3,'0')||'.'||
      lpad(substr(ip,instr(ip,'.')+1,instr(ip,'.',1,2)-instr(ip,'.')-1),3,'0')||'.'||
      lpad(substr(ip,instr(ip,'.',1,2)+1,instr(ip,'.',1,3)-instr(ip,'.',1,2)-1),3,'0')||'.'||
      lpad(substr(ip,instr(ip,'.',1,3)+1),3,'0');
    
    IP              FORMATTED_IP
    --------------- ---------------
    3.3.100.20      003.003.100.020
    3.20.3.20       003.020.003.020
    3.20.100.100    003.020.100.100
    3.100.100.20    003.100.100.020
    20.3.20.3       020.003.020.003
    20.3.100.3      020.003.100.003
    20.20.100.3     020.020.100.003
    100.3.3.3       100.003.003.003
    100.100.3.20    100.100.003.020
  • Laurent Schneider
    Laurent Schneider Member Posts: 5,219
    I prefer using lpad than to_number because if some values are invalid, the to_number will generate an error, but one may prefer to receive an error
  • 121256
    121256 Member Posts: 1,054
    Someone could prefer general and scalable solution using order member function. Example is [url http://www.sql.ru/forum/actualthread.aspx?bid=3&tid=339732&pg=-1&hl=order+siblings#3156237]here
  • 573579
    573579 Member Posts: 2
    great, it works, many thanks to all.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited November 2010
    Same thread of OTN-Japan
    http://otn.oracle.co.jp/forum/thread.jspa?threadID=35000486

    and my site :-)
    http://www.geocities.jp/oraclesqlpuzzle/10-152.html
    col afterIP for a15
    WITH T AS (
    SELECT '83.133.96.2' IP FROM DUAL UNION ALL
    SELECT '83.133.96.3' FROM DUAL UNION ALL
    SELECT '83.133.96.10' FROM DUAL UNION ALL
    SELECT '83.133.98.10' FROM DUAL UNION ALL
    SELECT '83.133.97.2' FROM DUAL UNION ALL
    SELECT '83.133.97.3' FROM DUAL UNION ALL
    SELECT '83.133.97.10' FROM DUAL UNION ALL
    SELECT '83.133.98.2' FROM DUAL UNION ALL
    SELECT '83.133.98.3' FROM DUAL UNION ALL
    SELECT '83.133.96.20' FROM DUAL )
    select IP,
    RegExp_Replace(RegExp_Replace(Ip,'(^|\.)','\1000')
                  ,'(^|\.)[0-9]*([0-9]{3})'
                  ,'\1\2') as afterIP
    from T
    order by afterIP;
    
    IP            AFTERIP
    ------------  ---------------
    83.133.96.2   083.133.096.002
    83.133.96.3   083.133.096.003
    83.133.96.10  083.133.096.010
    83.133.96.20  083.133.096.020
    83.133.97.2   083.133.097.002
    83.133.97.3   083.133.097.003
    83.133.97.10  083.133.097.010
    83.133.98.2   083.133.098.002
    83.133.98.3   083.133.098.003
    83.133.98.10  083.133.098.010
    This query is used archaic Logic of VBScript of ASP.
    That provides Format Function.
    right("000" & VAL,3)
  • cd_2
    cd_2 Member Posts: 5,021
    Looks a bit like 437109 ... ;-)

    C.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    3 years after
    There is simpler new version :8}
    4520585
This discussion has been closed.