1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Complex SQL Question

Discussion in 'SQL PL/SQL' started by prithviraaj, Jan 28, 2009.

  1. prithviraaj

    prithviraaj Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    I have a table and I want to get the data from table having last 9 digits of a column (having datatype as varchar2).But the condition is last 7 digits should be same.
    e.g.Below given data should be output if the data of above criteria is present. Last 7 digits are same (2121545)in all the given 3 numbers
    542121545
    872121545
    292121545
    See above last 7 digits are same. I want to get the above data in similar fashion. Which SQL query i should use. (means aggregate functions,joins,union etc what is the way ...i am just stuck)
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Here is a clue .

    You must rely on,
    Analytical function
    String function

    Try to make from the following piece of code.

    Code (Text):

    SQL> select * from DIG_TEST ;

    COL1
    ----------
    891234567
    991234567
    123456709
    12345670
    1234567
    111234567
    891234568
    991234568
    111234568

    9 rows selected.

    SQL> SELECT COL1 ,SUBSTR(COL1,-7) "7DIGIT" ,
      2         ROW_NUMBER() OVER (PARTITION BY SUBSTR(COL1,-7) ORDER BY ROWNUM) RNK
      3  FROM DIG_TEST
      4  WHERE LENGTH(COL1)=9;

    COL1       7DIGIT         RNK
    ---------- ------- ----------
    891234567  1234567          1
    991234567  1234567          2
    111234567  1234567          3
    891234568  1234568          1
    991234568  1234568          2
    111234568  1234568          3
    123456709  3456709          1

    7 rows selected.

    SQL>