Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2014
    Posts
    3

    Exclamation Unanswered: Query from a table

    Hello ,
    I have this table with this records

    mov.year mov.cust0merid mov.productid mov.price mov.days
    2014 c452 5500 3.50 20140101
    2014 c452 5500 3.35 20140130
    2014 c452 5500 3.20 20140201
    2014 c501 5500 6.20 20140601
    2014 c501 5602 3.75 20140401

    I would like to do a query show me the last price for each customer and productid
    The results:

    2014 c452 5500 3.20 20140201
    2014 c501 5500 6.20 20140601
    2014 c501 5602 3.75 20140401

  2. #2
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6

  3. #3
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    SELECT A.*
    FROM your_table_name A,
    (
    SELECT year, customerid, productid, MAX(days) AS days
    FROM your_table_name B
    GROUP BY year, customerid, productid
    ) B
    WHERE A.year = B.year
    AND A.customerid = B.customerid
    AND A.productid = B.productid
    AND A.days = B.days
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  4. #4
    Join Date
    Dec 2014
    Posts
    3
    Quote Originally Posted by aflorin27 View Post
    SELECT A.*
    FROM your_table_name A,
    (
    SELECT year, customerid, productid, MAX(days) AS days
    FROM your_table_name B
    GROUP BY year, customerid, productid
    ) B
    WHERE A.year = B.year
    AND A.customerid = B.customerid
    AND A.productid = B.productid
    AND A.days = B.days
    Hi Florin
    thanks for your help
    I write so the query :
    select MOV.ESERCIZIO, MOV.NUMEROPROT, MOV.DATAPROT, MOV.ARTICOLO, MOV.CLI_FOR,MOV.TIPOPROT,MOV.EPRNETTO
    from DB2INST1.MOVIMAG MOV, DB2INST1.MOVIMAG MOV1,
    (select MOV1.ESERCIZIO, MOV1.NUMEROPROT, max (MOV1.DATAPROT), MOV1.ARTICOLO, MOV1.CLI_FOR,MOV1.TIPOPROT,MOV1.EPRNETTO
    from DB2INST1.MOVIMAG MOV1
    Group by MOV1.NUMEROPROT,MOV1.ESERCIZIO, MOV1.ARTICOLO, MOV1.CLI_FOR,MOV1.TIPOPROT,MOV1.EPRNETTO
    )
    where
    MOV1.ESERCIZIO=MOV.ESERCIZIO AND
    MOV1.NUMEROPROT=MOV.NUMEROPROT and
    MOV1.DATAPROT=MOV.DATAPROT and
    mOV1.ARTICOLO=mOV.ARTICOLO and
    MOV1.CLI_FOR=MOV.CLI_FOR and
    MOV.CLI_FOR='AR088'AND
    mov1.esercizio='2015'and
    MOV.TIPOPROT='M5'AND
    MOV.ARTICOLO<>'UTF'

    But I have many records ,
    wath's wrong ?

  5. #5
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    probably a good idea to provide a sample of the output rather than say 'I have many records'

  6. #6
    Join Date
    Dec 2014
    Posts
    3
    Quote Originally Posted by tafster View Post
    probably a good idea to provide a sample of the output rather than say 'I have many records'
    Sorry I don't explain ,
    I choose one Custmerid as MOV.CLI_FOR='AR088
    I choose one Year as mov1.esercizio='2015
    and I must have 27 rows , insted I have 2500 rows.
    Thanks

  7. #7
    Join Date
    Aug 2012
    Posts
    19
    I want to update number field in table through query. In query its ok but how to update same field in Table through query
    For example I have field in Table Price Qty and total Through query it is ok in Query but it is not showing in the table. I calculated Total field through query like this ( =Qty+Price). How it appears in the table need your guidance

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •