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!

Help me to solve this query~~~

Discussion in 'SQL PL/SQL' started by virusx1984, Sep 14, 2010.

  1. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    I have a table which is named "A" and is showed as below:
    ==============A=================
    PN Factory Plant Mount
    1 E01033G SG Chennai 164868
    2 E01033I SG Dongguan 368476
    3 E01033G SG Dongguan 1498716
    4 E01033I SG Reynosa 39008
    5 E01033G QHD Chennai 500676
    6 E01033I QHD Chennai 795924
    7 E01033I QHD Reynosa 365540
    ==============A=================

    How to write the SQL ,and get the result as below after executing the SQL:
    ===============B================
    PN Factory Plant Mount
    1 E01033G SG Chennai 164868
    2 E01033I SG Dongguan 1867192
    3 E01033I SG Reynosa 39008
    4 E01033I QHD Chennai 1296600
    5 E01033I QHD Reynosa 365540
    ================B===============
    Please note that the 2nd row whose Factory and Plant is the same as the 3rd row's has a Pn value - "E01033I" of which the 6 leftist character has the same value as 3rd row's Pn value -"E01033I" that will be combined with the 3rd row into one row which is showed in the "B" table's 2nd row.
    And the additional rule of the combination above is that the Pn Value showed in the "B"(result) table of which the rightist character has the largest ASCII code of Pn's of the rows which is needed to combined.
    In this situation,the 2nd row and 3rd row of the Table A need to combine into one row.The "E01033I" is showed in the Pn of the result row,because the ASCII code of "I" is 73 which is bigger than the ASCII code of "G"(71).
    Finally,the result row's Mount is the summary of 368476 and 1498716.

    tks a lot.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What query have you written to solve this problem? Please post it so we can see what you've done and possibly make corrections and/or suggestions.
     
  3. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    The problem is I don't know how to write it,and I want to know how to write it.
    I just know the result Table is B ,if the query is right.
    It just like the function y=f(x). If the x has been given,the y can be calculate. I've know the x list and the y list (result),but I didn't know the "f()". So could you help me?
     
  4. sennen

    sennen Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    You could use:
    Code (SQL):
    SELECT MAX(pn), factory, plant, SUM(mount)
    FROM your_table
    GROUP BY factory, plant
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Iif this is a homework assignment you really need to try to write this yourself and post what you've written.

    Someone can then assist you.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I suppose he or she could, however the results are not in the desired order and, if this is an assignment for school, it isn't proper for us to write such queries for those who ask and show no work on the problem.
     
  7. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    "Select Last(PN),Factory,Plant,Sum(Mount) From A Group by Factory,Plant"

    This SQL which I've written in Access can get the result I wanted,but it dosen't work in Oracle DB,because there is no "Last()" function in Oracle which Access has.

    Is there any alternative function in Oracle?
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Replace LAST() with MAX() and it should work.
     
    virusx1984 likes this.
  9. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    thank you for your help!! The problem has been solved.