SQL error on ORDER BY in subquery (TOP is used)

Question

I am getting Syntax error near 'ORDER' from the following query:

SELECT i.ItemID, i.Description, v.VendorItemID
FROM Items i 
JOIN ItemVendors v ON
    v.RecordID = (
                 SELECT TOP 1 RecordID
                 FROM ItemVendors iv
                 WHERE
                     iv.VendorID = i.VendorID AND
                     iv.ParentRecordID = i.RecordID
                 ORDER BY RecordID DESC
                 );

If I remove the ORDER BY clause the query runs fine, but unfortunately it is essential to pull from a descending list rather than ascending. All the answers I have found relating to this indicate that TOP must be used, but in this case I am already using it. I don't have any problems with TOP and ORDER BY when not part of a subquery. Any ideas?

Answer 1

I'd use max instead of top 1 ... order by

SELECT i.ItemID, i.Description, v.VendorItemID FROM Items i JOIN ItemVendors v ON v.RecordID = ( SELECT max(RecordID) FROM ItemVendors iv WHERE iv.VendorID = i.VendorID AND iv.ParentRecordID = i.RecordID);