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!

Oracle database

Discussion in 'SQL PL/SQL' started by keshav jain, Jul 14, 2014.

  1. keshav jain

    keshav jain Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    hiii

    I have a table in which i have two column Dealer_cde and month.
    there are no of dealer in my table and each dealer have multiple entry in the table.
    so my query is that i want to fetch 5 records for each dealer

    I have to create a S.P. in which i have to use it

    So please help me

    Its urgent....

    Thanx
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Keshav,

    Will something like this work for you:

    Code (SQL):

    SQL> WITH my_dealers AS
    (
        SELECT
             MOD(level+3, 3) dealer_code,
             TO_CHAR(ADD_MONTHS(SYSDATE, 12*level), 'YYYY')  YEAR
        FROM  dual
        CONNECT BY level <= 20
    )
    SELECT dealer_code, YEAR
    FROM my_dealers
    ORDER BY dealer_code;


    DEALER_CODE YEAR
    ----------- ----
              0 2023
              0 2026
              0 2017
              0 2032
              0 2029
              0 2020
              1 2021
              1 2018
              1 2015
              1 2024
              1 2033
              1 2030
              1 2027
              2 2028
              2 2019
              2 2016
              2 2031
              2 2022
              2 2025
              2 2034

    20 ROWS selected.



    SQL> WITH my_dealers AS
    (
        SELECT
             MOD(level+3, 3) dealer_code,
             TO_CHAR(ADD_MONTHS(SYSDATE, 12*level), 'YYYY')  YEAR
        FROM  dual
        CONNECT BY level <= 20
    )
    SELECT dealer_code, YEAR
    FROM (
      SELECT
          d2.dealer_code, d2.YEAR,
          RANK() OVER (PARTITION BY dealer_code ORDER BY YEAR) rank
      FROM my_dealers d2
    )
    WHERE rank <=5
    ORDER BY dealer_code;


    DEALER_CODE YEAR
    ----------- ----
              0 2017
              0 2020
              0 2023
              0 2026
              0 2029
              1 2015
              1 2018
              1 2021
              1 2024
              1 2027
              2 2016
              2 2019
              2 2022
              2 2025
              2 2028

    15 ROWS selected.
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Provide sample data and expected output
     
  4. keshav jain

    keshav jain Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Note: .................... is significance of space


    sample data

    code.................period
    001....................2
    002....................9
    003....................10
    003....................1
    003....................9
    002....................7
    001....................1
    001....................3
    001....................4
    003....................2
    002....................8
    003....................3
    002....................6
    001....................7
    003....................4
    002....................5
    001....................6
    002....................3
    003....................5
    001....................8
    002....................4
    003....................6
    003....................12
    003....................7
    002....................2
    002....................1
    001....................9
    001....................5
    002....................10

    sample output

    code....................period
    001....................9
    001....................8
    001....................7
    001....................6
    001....................5
    002....................10
    002....................9
    002....................8
    002....................7
    002....................6
    003....................12
    003....................10
    003....................9
    003....................7
    003....................6
     
  5. keshav jain

    keshav jain Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    can you elaborate it....
     
  6. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Keshav,

    Try the following:

    Code (SQL):
    WITH my_dealers AS
    (
    SELECT '001' code, '2'  period FROM DUAL UNION  
    SELECT '002' code, '9'  period FROM DUAL UNION  
    SELECT '003' code, '10'  period FROM DUAL UNION  
    SELECT '003' code, '1'  period FROM DUAL UNION  
    SELECT '003' code, '9'  period FROM DUAL UNION  
    SELECT '002' code, '7'  period FROM DUAL UNION  
    SELECT '001' code, '1'  period FROM DUAL UNION  
    SELECT '001' code, '3'  period FROM DUAL UNION  
    SELECT '001' code, '4'  period FROM DUAL UNION  
    SELECT '003' code, '2'  period FROM DUAL UNION  
    SELECT '002' code, '8'  period FROM DUAL UNION  
    SELECT '003' code, '3'  period FROM DUAL UNION  
    SELECT '002' code, '6'  period FROM DUAL UNION  
    SELECT '001' code, '7'  period FROM DUAL UNION  
    SELECT '003' code, '4'  period FROM DUAL UNION  
    SELECT '002' code, '5'  period FROM DUAL UNION  
    SELECT '001' code, '6'  period FROM DUAL UNION  
    SELECT '002' code, '3'  period FROM DUAL UNION  
    SELECT '003' code, '5'  period FROM DUAL UNION  
    SELECT '001' code, '8'  period FROM DUAL UNION  
    SELECT '002' code, '4'  period FROM DUAL UNION  
    SELECT '003' code, '6'  period FROM DUAL UNION  
    SELECT '003' code, '12'  period FROM DUAL UNION  
    SELECT '003' code, '7'  period FROM DUAL UNION  
    SELECT '002' code, '2'  period FROM DUAL UNION  
    SELECT '002' code, '1'  period FROM DUAL UNION  
    SELECT '001' code, '9'  period FROM DUAL UNION  
    SELECT '001' code, '5'  period FROM DUAL UNION  
    SELECT '002' code, '10'  period FROM DUAL
    )
    SELECT code, period
    FROM (
      SELECT
          d2.code, d2.period,
          RANK() OVER (PARTITION BY code ORDER BY TO_NUMBER(period) DESC) rank
      FROM my_dealers d2
    )
    WHERE rank <=5
    ORDER BY code;
    You just need to run the last portion (I don't have your tables in my instance).

    Code (SQL):
    SELECT code, period
    FROM (
      SELECT
          d2.code, d2.period,
          RANK() OVER (PARTITION BY code ORDER BY TO_NUMBER(period) DESC) rank
      FROM my_dealers d2
    )
    WHERE rank <=5
    ORDER BY code;