>>>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.
Declare @recsec integer
Select @recsec= Select Max(recseq) From webprddt6.drawmfai Where part no = '860637-001' Group By partno
Select recsec,failoc,faistatus from webprddt6.drawmfai where recsec=@recsec
or substitute valriable with 1st Select in the 2nd one. It all depends if back-end supports it.
Edward Pikman
Independent Consultant