>>Not sure whether you mean "Any previous invoice detail record" or "The previous invoice detail record". Your example in another post just selects the TOP 1 of any record where NoStatus < > 7
>>
>>But I'd give it a shot:
>>To get the invoiceDetails were for the last record NoStatus = 7 and has any previous record with NoStatus = 3
>>
>>
>>SELECT InvoiceDetail.NoInvoice,InvoiceDetail.Numero
>> FROM InvoiceDetail I
>> WHERE NoStatus = 7 AND NOT EXISTS(SELECT 1 FROM InvoiceDetail WHERE I.NoInvoice = NoInvoice AND Numero > I.Numero)
>> AND EXISTS(SELECT 1 FROM InvoiceDetail WHERE I.NoInvoice = NoInvoice AND NoStatus = 3)
>>
>>
>>If it needs to be the previous record and numero is one less than numer of the last record
>>
>>
>>SELECT InvoiceDetail.NoInvoice,InvoiceDetail.Numero
>> FROM InvoiceDetail I
>> WHERE NoStatus = 7 AND NOT EXISTS(SELECT 1 FROM InvoiceDetail WHERE I.NoInvoice = NoInvoice AND Numero > I.Numero)
>> AND EXISTS(SELECT 1 FROM InvoiceDetail WHERE I.NoInvoice = NoInvoice AND NoStatus = 3 AND Numero = I.Numero-1)
>>
>>
>>Is this what you are looking for ?
>
>This is very good. Thanks
Note that the above contains an error (I did wrote it from the top of my head), In the select list, omit the InvoiceDetail. prefix. Also note that you can easily replace SELECT list with DELETE to inmediately delete the records.
But I guess you'd figure this out yourself
Walter,