Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2012
    Posts
    16

    Unanswered: Need help retrieving highest paired value

    I am attempting to create a report that will display all the yearly, and monthly gas production for a company but the issue I am encountering is finding amended records for a lease. Below is what the query produces:

    Company Number Year Month Gas Production Lease Number
    3125 2009 1 296 9105701
    3125 2009 1 345 165207
    3125 2009 1 565 2250593 <--
    3125 2009 1 1161 2250593

    I would like to omit the lower production gas production, and keep the higher production from the same lease.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2012
    Posts
    16
    This is the original code:

    select distinct [CODING-MAST-OP-NUMBER], YEAR, MONTH, [GAS-WELL-GAS PRODUCTION], RRCID
    from Gas_Prod2009a
    where [CODING-MAST-OP-NUMBER] = 3125
    Order by MONTH asc, [GAS-WELL-GAS PRODUCTION] asc


    Will this code work, cause it isn't working for me:

    select MAX (ALL | distinct) [CODING-MAST-OP-NUMBER], YEAR, MONTH, [GAS-WELL-GAS PRODUCTION], RRCID
    from Gas_Prod2009a
    where [CODING-MAST-OP-NUMBER] = 3125
    Order by MONTH asc, [GAS-WELL-GAS PRODUCTION] asc

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try:
    Code:
    SELECT [CODING-MAST-OP-NUMBER], [YEAR], [MONTH]
    ,  Max([GAS-WELL-GAS PRODUCTION]) AS [GAS-WELL-GAS PRODUCTION], RRCID
       FROM Gas_Prod2009a
       WHERE [CODING-MAST-OP-NUMBER] = 3125
       GROUP BY [CODING-MAST-OP-NUMBER], [YEAR], [MONTH], RRCID
       ORDER BY [MONTH], [GAS-WELL-GAS PRODUCTION]
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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