>>I need three fields from a remote data source.
>>
>>This is a data table of First Article Inspection records for our product part numbers.
>>
>>One part number can have multiple FAI's.
>>
>>RECSEQ = Sequiential Record Number
>>FAILOC = FAI location
>>FAISTATUS (Could be a 1 or 2)
>>
>>This SQL does not work because I don't have failoc or faistatus in the Group By section:
>>
>>Select Max(recseq) As recseq, failoc, faistatus From webprddt6.drawmfai Where part no = '860637-001' Group By partno
>>
>>This SQL does not work because it gives me the MAX recseq for the faistatus 1 and another for faistatus 2:
>>
>>Select Max(recseq) As recseq, failoc, faistatus From webprddt6.drawmfai Where part no = '860637-001' Group By partno, failoc, faistatus
>>
>>Please help.
>
>One way is
>
>Select MaxRecs as recseq, dr.failoc, dr.faistatus ;
>From webprddt6.drawmfai dr ;
>inner join (select Max(RecSeq) as MaxRecs, partno group by 2 ;
>from webprddt6.drawmfai where partno = '860637-001' ) MaxRec ;
>on Dr.PartNo = MaxRec.PartNo
Actually, this would not work. Give me 5 minutes to make it correct.
If it's not broken, fix it until it is.
My Blog