How to Group By first and then Select the Max record in each group

[OceanDesigner]

I thought this was going to be fairly straightforward, but I can't seem to sort it out. I have a table with an equipment number and operating hours for that equipment along with several other attributes. I want to write a query that first groups the table according the equipment number and then select the record with the maximum number of op hours for each equipment. Also, I would like the entire record returned, not just the hours. The table looks something like this:

tblOpHours:

EqNum OpHours fld1 fld2
1 3 a b
1 10 c d
2 4 e f
2 15 g h
3 1 i j
3 20 k l

The query I want to write should return the following:

EqNum OpHours fld1 fld2
1 10 c d
2 15 g h
3 20 k l

I appreciate the help. - Jeff

 

[ITMorpheus]

I had the same problem once, here's how I solved it:
Assuming that tbl1 is the table that holds your data, run this query:

SELECT tbl1.EqNum, tbl1.OpHours,tbl1.fld1,tbl1.fld2
FROM tbl1 INNER JOIN
(
SELECT EqNum,MAX(OpHours) OpHours
FROM tbl1
GROUP BY EqNum) A
ON tbl1.EqNum=A.EqNum AND tbl1.OpHours=A.OpHours

It first groups the records by EqNum and get the Max, then inner joins on the source table and gets the rest of the information.
The only potential problem (It may not be actually depending on the behavior that you expect) is that if you have something like:

EqNum OpHours fld1 fld2
1 3 a b
1 10 c d
2 4 e f
2 15 g h
3 1 i j
3 20 k l
3 20 u s

(I have added the last column to your original table: it's a record where eq num and OpHours are the same) it will return something like:

EqNum OpHours fld1 fld2
3 20 k l
3 20 k h
2 15 g h
1 10 c d

IT will return BOTH records having 3 as eqnum and 20 as ophours. Assuming this is what yoiu want you have no problem. LEt me know how it works out.
Hapy programming!

 

[maswien]

Try this:

select EqNum,  
       (select max(OpHours) 
        from myTable
        where EqNum=t0.EqNum) as OpHours,
       fld1,  
       fld2
from myTable t0

 

[OceanDesigner]

ITMorpheous, your solution works. Unfortunately, I do have records like the ones you point out. So I used your code twice, the first to filter as we have discussed, and the second (which feeds off of the first) to filter in the exact same way, but use a date discriminator instead of OpHours. I finally got a chance to run it and it seems to give the correct results. That is a lot more complicated that I would have expected.

Thanks, Jeff