Instead on Sub-query use an INNER JOIN with derived table (it's going to be quicker),
add your extra (calculated) field into the list of the fields and use ORDER BY 1.
e.g.
SELECT Sales.* ;
FROM Sales INNER JOIN (SELECT TOP 2 iSaleid, PADL(cInvoiceNo, 11) FROM Sales ORDER BY 2 DESC) tempOrd
on Sales.iSaleID = tempOrd.iSaleID
would it work?
>Hi,
>
>I have a query like this:
>
>
SELECT Sales.* ;
> FROM Sales ;
> WHERE iSaleID IN (SELECT TOP 2 iSaleid FROM Sales ORDER BY cInvoiceNo DESC)
>
>The problem I'm having is due to the fact that if I have invoices entered with cInvoiceNo from "1" to "11", I want Invoices "10" and "11" to be returned, but I'm getting Invoices "1" and "11". So I really want to do something like this:
>
>
SELECT Sales.* ;
> FROM Sales ;
> WHERE iSaleID IN (SELECT TOP 2 iSaleid FROM Sales ORDER BY PADL(cInvoiceNo, 11) DESC)
>
>but of course that gives me a syntax error.
>
>Any ideas what I can do? I have no control over the invoice numbers as they are being returned from QuickBooks.
>
>Thanks,
If it's not broken, fix it until it is.
My Blog