>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
If it's not broken, fix it until it is.
My Blog