Need help retrieving highest paired value

tech_wiz2008 · #1 (**permalink**) 01-12-12, 17:20

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.

Pat Phelan · #2 (**permalink**) 01-12-12, 17:29

MAX (Transact-SQL)

-PatP

tech_wiz2008 · #3 (**permalink**) 01-12-12, 17:39

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

Pat Phelan · #4 (**permalink**) 01-12-12, 23:15

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

Internet Services

Web Development

Digital Marketing

Consumer Tech